Address and Vlookup

[Guest]

I have a workbook which contains depreciation schedules for various assets.
I need to create a summary schedule that looksup the monthly depreciation
expense for each asset.

Each individual asset's worksheet is layed out identically, with the
database I'm searching in the range B14109.

Assuming that column D contains the workseet name and that cell G8 contains
the lookup value, I've entered the following formula:

=VLOOKUP($G$8,CONCATENATE(ADDRESS(14,2,1,3,TEXT($D9,"0")),":$D$109"),1,FALSE)

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?

Thanks Much.

 

[Harlan Grove]

sjring wrote...
....

Each individual asset's worksheet is layed out identically, with the
database I'm searching in the range B14109. ....
=VLOOKUP($G$8,CONCATENATE(ADDRESS(14,2,1,3,TEXT($D9,"0")),":$D$109"),1,FALSE)

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")&"'!B14109"),1,0)

 

[Trevor Shuttleworth]

You will need to use INDIRECT.

For example:

=INDIRECT(D$5 &"!J122")

It would look something like:

=VLOOKUP($G$8,INDIRECT(ADDRESS(14,2,1,3,TEXT($D9,"0"))&":$D$109"),1,FALSE)

but I don't know exactly what you were trying to concatenate for the end
result.

Search the archives for other examples

Regards

Trevor

 

[Guest]

Thanks, That did the trick.

sjring wrote...
....

Don't screw around with either CONCATENATE or ADDRESS. Use
=VLOOKUP($G$8,INDIRECT("'"TEXT($D9,"0")&"'!B14109"),1,0)

 

[Guest]

hi harlan ,
i have seen so many questions about vlooku and concatenate function and they
suggested to use indirect or use pull funciton created by you,
can you redefine your macro as per my requirement please , as i ma not that
good with vba codes.

Thanks
please reply i will send you my excel data.

 

[Harlan Grove]

I screwed up the formula above. It should have been

=VLOOKUP($G$8,INDIRECT(TEXT($D9,"\'0\'")&"!B14109"),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.