How to enter a range address using data elsewhere on the sheet?

[Guest]

I want to enter cell addresses and range addresses in formulas (functions)
such as vlookup using row and column numbers that are calculated elsewhere on
the spreadsheet. How do I write the range formula in a manner that takes its
cell address parameters from those other cells?

 

[Guest]

Precede the cell address with INDIRECT, such as INDIRECT(A1) will result in
the formula evaluating to say G15, if A1 contains text "G15"

 

[Ardus Petus]

Try INDIRECT

HTH

 

[Aladin Akyurek]

=VLOOKUP(...,INDEX(Range,X2,Y2),...)

 

[Guest]

Kassie,
This looks to be the Rosetta Stone for me. Thanks. I still need to do some
experimenting with it, but a quick check says it works.

 

[Peo Sjoblom]

If you can avoid indirect by for instance using index (index can use other
cells as row and column numbers) than you should do that. indirect is a
volatile function so it slows down a workbook since they recalculate where
others don't

http://www.decisionmodels.com/calcsecretsi.htm

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon