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


"sjring" wrote in message
...
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 B14:D109.

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($D 9,"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.