Conditional Array Reference inside of other Formula

Posted by JuliaB on March 02, 2001 4:13 PM

I need to create a reference an array range inside
a formula where the array range is conditional on
another cell.

Using the following:
=Address(5+Match(Matchcell,Range,1),14,4)

I can get the cell reference (say N10) I need as a cell. But then I need to use this result as the start
of an array range inside of another formula (N10:017) and I can't figure it out.

Any ideas?

Posted by David Hawley on March 02, 2001 5:25 PM

Hi Julian

You would use the INDIRECT function for this. Lets say you cell address "N10" is in A1, you would use this as the starting point of a range inside another formula like this:

=SUM(INDIRECT(A1&":O17"))

BTW you could just type: N10 in cell A1

Dave

OzGrid Business Applications