dynamic - Refer to an excel named range on another sheet using indirect -
let's have 3 sheets (let's sheet1
, sheet2
, sheet3
).
sheet1
, sheet2
contain each sheet-level range named myrange
. in sheet3
, if want access myrange
sheet1
, able use ='sheet1'!myrange
.
but now, want sheet3
contain generic code , able refer either sheet1
or sheet2
. expect able achieve same thing using
=indirect("'" & mysheetname & "'!myrange")
however, error #ref
when that.
- what do wrong ?
- how can work around ?
if myrange consists of multi-row, multi-column range, make sure pressed ctrl-shift-enter
when entered =indirect("'" & mysheetname & "'!myrange")
. if forget use ctrl-shift-enter
, #value
error. however, stated have #ref
.
using code single-cell range, result looking for. way can #ref
error using wrong sheet name. suspect problem. check sheet name , repost more detail if still doesn't work.
Comments
Post a Comment