INDIRECT and multiple cells at once

  • Thread starter Thread starter Billy
  • Start date Start date
B

Billy

Anybody know is it possible to write string which contain multiple
cells and then evaluate that.
For example with INDIRECT you can do:
If Sheet1 contain in A1 value 10 and Sheet2 contain in A2 value 20
I can do this:
=INDIRECT("Sheet1!A1") and I get 10
But it doesn't work that:
=INDIRECT("Sheet1!A1+Sheet2!A2"). I get #REF!

So Question is it possible to evaluate string expression like this
"A1+A2/2". Anybody have some idea how to calculate strings which have
more cell references?

Billy
 
I think you need to do an Indirect on each of the references separately
=INDIRECT("Sheet2!C14")+INDIRECT("Sheet3!B15")
or
=SUM(INDIRECT("Sheet2!A1"):INDIRECT("Sheet2!A6"))

HTH

RES
 
That I know that is an option, but my goal is that user can write
arbitrary string formula on template sheet and then will be calculated
regarding to that rule in string of each cell. And I don't know in
advance if that string will be "A1+A2" or just "A2" for example.

Billy
 
Billy wrote...
That I know that is an option, but my goal is that user can write
arbitrary string formula on template sheet and then will be calculated
regarding to that rule in string of each cell. And I don't know in
advance if that string will be "A1+A2" or just "A2" for example.
Click to expand...

Excel doesn't provide that functionality built in. Since it appears
you're using VBA anyway, you'd need to use VBA's Evaluate to evaluate
the formulas entered as text.

The reason INDIRECT("foo!X99") *APPEARS* to return the value of foo!X99
is because it actually returns a range reference to foo!X99, and when
given a range reference as an arithmetic expression (and in the simple
formula =X, X is a degenerate arithmetic expression) Excel uses the
..Value property of the range.

The reason INDIRECT("foo!X99+bar!Z1") fails is because foo!X99+bar!Z1
isn't a valid string representation of a range reference.
 
Back
Top