vlookup concatenated reference
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.
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.