Variable reference within a formula

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Is it possible to concatenate a reference string within a
formula to enable the reference to become a variable ie.
replace the string "[Book2]Sheet2!" in =SUM([Book2]Sheet2!
$A$1:$A$2) with a cell reference containing this or
another valid reference.
I have tried using the "&" to concatenate within a
formula without success
 
You can use INDIRECT for this;

E.g. in D1 enter: Sheet2!A1:A100 as text (don't begin with an = sign or +)

for your formula use

=SUM(INDIRECT(D1))

and that will sum A1:A100 from sheet2.

note - haven't tested it on outside workbooks however, give it a shot!
 
Back
Top