Replacing cell referance with value in INDIRECT-formula
HebbeLille wrote...
Can anyone please guide me on how to change part of cell reference in this
formula:
=INDIRECT("'[" & $A$19 & "]Sheet1'!$B$2")
The formula is in Sheet2, and I would like to change the reference $B$2 with
the value from cell H1 in sheet2.
I have tried with =INDIRECT("'[" & $A$19 & "]Sheet1'!$B$(=INDIRECT( H1)"),
but it gives error #REF.
....
You don't need the $s in the INDIRECT call. You could use
=INDIRECT("'[" & $A$19 & "]Sheet1'!B" & H1)
if transition-formula evaluation is disabled, or
=INDIRECT("'[" & $A$19 & "]Sheet1'!B" & TEXT(H1,"0"))
just to be safe.
If you have fewer than 30 different worksheet names that could appear
in cell A19, you don't even need to use INDIRECT. For example, you
could use something like
=INDEX(CHOOSE(MATCH($A$19,ListOfWorkbookNames,0),S heet1!$B:$B,Sheet2!
$B:$B,...,Sheet29!$B:$B),H1)
|