Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Address and Vlookup
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. |
#2
|
|||
|
|||
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,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")&"'!B14:D10 9"),1,0) |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
Thanks, That did the trick.
"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,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")&"'!B14:D10 9"),1,0) |
#5
|
|||
|
|||
Address and Vlookup
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" 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,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")&"'!B14:D10 9"),1,0) |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions |