Address and Vlookup
"darsg" wrote...
"Harlan Grove" wrote:
sjring wrote...
....
Each individual asset's worksheet is layed out identically, with the
database I'm searching in the range B14:D109.
....
=VLOOKUP($G$8,CONCATENATE(ADDRESS(14,2,1,3,TEXT ($D9,"0")),":$D$109"),1,FA
LSE)
The cell is returning a #Value error, I suspect because the
concatenate function is returning a string and not a cell range.
Any ideas on how I can vary the sheet name based on the value in
cell D9?
Don't screw around with either CONCATENATE or ADDRESS. Use
=VLOOKUP($G$8,INDIRECT("'"TEXT($D9,"0")&"'!B14:D 109"),1,0)
I screwed up the formula above. It should have been
=VLOOKUP($G$8,INDIRECT(TEXT($D9,"\'0\'")&"!B14:D10 9"),1,0)
The main point is that you appear to be looking up a value in a different
worksheet in the *same* workbook. If that's the case, INDIRECT is always
sufficient, and my pull function would be superfluous.
The minor points are that the & concatenation operator is usually a better
idea than the CONCATENATE function because the former is shorter, has no
limit on operands other than formula length, and doesn't waste a nested
function call level; and there's never a good reason to use ADDRESS calls
inside INDIRECT calls - there's always an equivalent way using INDIRECT
alone with R1C1 addressing.
|