vlookup with concatenated multiple references

immyjimmy

Active Member
Joined
May 27, 2002
Messages
254
I have a workbook which keeps track of all the reports I run on all our projects. Every two weeks, I run a report on the budget hours vs. the actual hours logged for each project. And I keep the date of the last time I ran each report.
I name the report "BVH 'sales order number' 'customer name' 'date - in yymmdd format'

example:
BVH 4000265 ACME 060614.xls

Now one of these projects is mine and I need to keep closer track of it than all the rest. So I have a workbook for that project with all the contract line items listed, sequence numbers, priorities, release numbers and - budgeted hours per line item. I also have a formula set in one column to do a vlookup in the last BVH report file to compare actual hours spent with the budgeted hours allocated. If the budget is lower than the actual, I have conditional formating set to turn the line item green if budget is higher than actual and pink if actual exceeds budget.

In cell J3, I have the following formula:
=VLOOKUP(A2,'T:\JIM C\EXCEL\[Budget VS Hours to do list.xls]Today'!$B$2:$F$15,5,0)

This shows the date of the last BVH report run. Cell A2 shows the sales order number.

In cell K3, I have the following formula:
=IF(ABS(RIGHT(YEAR($J3),2))<10,"0"&ABS(RIGHT(YEAR($J3),2)),ABS(RIGHT(YEAR($J3),2)))

This gives me the year in a two digit format

In cell L3, I have the following formula:
=IF(ABS(RIGHT(MONTH($J3),2))<10,"0"&ABS(RIGHT(MONTH($J3),2)),ABS(RIGHT(MONTH($J3),2)))

This gives me the month in a two digit format

In cell M3, I have the following formula:
=IF(ABS(RIGHT(DAY($J3),2))<10,"0"&ABS(RIGHT(DAY($J3),2)),ABS(RIGHT(DAY($J3),2)))

This gives me the day in a two digit format

In cell N3, I have the following formula:
=K3&L3&M3

This concatenates yymmdd in the format I use in my BVH report title.

Cell J2 has the following formula:
="'T:\JIM C\EXCEL\[BVH "&A2&K4&" "&$N$3&".xls]Sheet1'!$C$2:$J$1000"

This gives me the path, name and range I want to search for my actual hours logged.
A2 is my sales order number.
K4 has the customer's name with a space in front of it.
N3 is the concatenated date from above.

Lastly, I 'WANT' the formula in the rest of column J to read something like this:
=VLOOKUP(E4&" TOTAL",J2,8,0)
or
=VLOOKUP(E4&" TOTAL",INDIRECT(J2),8,0)

But the only way I've gotten it to work is by typing:
=VLOOKUP(E4&" TOTAL",'T:\JIM C\EXCEL\[BVH 4000265 ACME 060531.xls]Sheet1'!$C$2:$J$1000,8,0)

How can I get the vlookup to accept the concatenated reference in J2?
As it is, I need to manually change the reference each time I have an updated report.

Thanks in advace...

Jim

Plan to be spontaneous tomorrow.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
You wrote:
In cell K3, I have the following formula:
=IF(ABS(RIGHT(YEAR($J3),2))<10,"0"&ABS(RIGHT(YEAR($J3),2)),ABS(RIGHT(YEAR($J3),2)))

I entered the date, 4/2 in J3, which shows as 4/2/06. Then, in K3, I entered:
=RIGHT(J3,2), and I got 06. Why do you need your large formula?

Likewise, in cell L3, I entered:
=IF(MONTH($J3)<10,"0"&MONTH($J3),MONTH($J3))
and got, 04. Same comment as before.

And, in cell M3, I entered:
=IF(DAY($J3)<10,"0"&DAY($J3),DAY($J3))
and got, 02. Same comment.

Finally, in cell N3, you enter:
=K3&L3&M3
which is fine.
=============================================
As far as your actual question, everything is fine, except that you have to add a space between A2 and K4 in your formula in J2, thus:
="'T:\JIM C\EXCEL\[BVH "&A2&" "&K4&" "&$N$3&".xls]Sheet1'!$C$2:$J$1000"

Give it a try.
 
Upvote 0
Ralph,

Thanks for the reply.

In response to the long formula inquiries, the date in my workbook comes in formatted MM/DD/YYYY. When I originally tried the =RIGHT(J3,2) formula, it returned the rightmost two digits in the date value - or "82". My formula returns the last two digits of the year. On the month and day formulas, I'm just adding a zero to returns of single digits. I got what I wanted with what I did. Maybe I just don't have my formatting correct.

As far as the actual problem of getting the vlookup formula to read the concatenated path in J2, the customer name K4 references already has a space in it. I use this because I have other places in the workbook that look up other files which other people write and use. Since the space is already there, I don't need to add the space in my formula. Just to be certain, I did try your solution, but still got my #N/A error.

So I'm still stuck...

Jim

We are born naked, wet, and hungry. Then things get worse.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top