Autoupdate the "Table Array/source" within a function

[draidr]

Ok this problem is clearly beyond me so I'll attempt to keep it short and sweet.

I have a function in the simplified following format.

=VLOOKUP(C$3,'H:\DailySpreadsheets\[Daily Adjuster_2011-01-11.xls]Sheet1'!$A$2:$B$300,2,FALSE)

The part in Bold (AKA Daily Adjuster_2011-01-11.xls) gets updated with the current date every day. The the formula itself works great but when I Drag-Fill this formula to the next day's cell, the Source worksheet doesn't update and I was wondering if there was a way to do that.

AKA the formula add's a day to the date, IE:
Daily Adjuster_2011-01-11.xls
Daily Adjuster_2011-01-12.xls
Daily Adjuster_2011-01-13.xls
..etc

I currently have to manually update the "date" on about 13 similar formulated worksheets and I would love it if there was a way to do it automatically. I have a column of the current date in the above format YYYY-MM-DD if this is needed to grab from elsewhere.

I know enough VB to get me in trouble so a basic guide would be helpful if that's needed as I suspect.

 

[dk]

You mentioned that you have each day's date in one of your columns. Assuming that the date is in cell A3 then you could use the INDIRECT function e.g.

=VLOOKUP(C$3,INDIRECT("'C:\temp\DailySpreadsheets\[Daily Adjuster_" & TEXT(A3,"yyyy-mm-dd")&".xls]Sheet1'!$A$2:$B$300"),2,FALSE)

The downside to this is that the source workbook must be open.

HTH
DK

 

[draidr]

You mentioned that you have each day's date in one of your columns. Assuming that the date is in cell A3 then you could use the INDIRECT function e.g.

=VLOOKUP(C$3,INDIRECT("'C:\temp\DailySpreadsheets\[Daily Adjuster_" & TEXT(A3,"yyyy-mm-dd")&".xls]Sheet1'!$A$2:$B$300"),2,FALSE)

The downside to this is that the source workbook must be open.

HTH
DK

Hmmm,
I copied the above and it didn't work, (correcting the A3 to the current cell the date is in and using the correct path for an open document) Excel says the formula is incorrect.

 

[dk]

Sorry, i don't have a H drive so I had to use a different path - did you change that? It should be something like this:

=VLOOKUP(C$3,INDIRECT("'H:\DailySpreadsheets\[Daily Adjuster_" & TEXT(A3,"yyyy-mm-dd")&".xls]Sheet1'!$A$2:$B$300"),2,FALSE)

DK

 

[draidr]

yes, I had changed the path

I get, "The Formula you typed contains an error."
everything from your new formula post is identical except for a change in the Date of the Cell. "EG1138" for the current date opened.

*Edit*

This is the current formula that won't work. My source document is open

=VLOOKUP(C$3,INDIRECT("'[Daily Adjuster_" & TEXT(EG1138,"yyyy-mm-dd")&".xls]Sheet1'!$A$2:$B$300"),2,FALSE)

I still get that formula error.

 

[dk]

Can you copy the formula to here? i.e. copy the formula that is causing the "Formula you typed contains an error" from the formula bar.

 

[draidr]

=IFERROR(VLOOKUP(C$1,INDIRECT(<wbr>"'[Adjuster_" & TEXT(EG1138,"yyyy-mm-dd)"&".<wbr>xls]Sheet1'!$A$2:$B$50,2,<wbr>FALSE),"")


This is the full formula, the IFERROR works normally and I only took it out to be less confusing.

 

[dk]

try this:

=IFERROR(VLOOKUP(C$1,INDIRECT("'[Adjuster_" & TEXT(EG1138,"yyyy-mm-dd")&".xls]Sheet1'!$A$2:$B$50"),2,FALSE),"")

 

[draidr]

Looks like that extra " before the &" did the trick. Thanks!

FYI, I don't mind having the sheets open for now, but I would love having another way to do this without having to have multiple worksheets up in the background.

This will do nicely for now. =) thanks

 

[dk]

Glad you got it working

There are some fudges around to get around the INDIRECT function needing to have the source workbooks open. If you search this board you will probably find plenty of info e.g.

http://www.mrexcel.com/forum/showthread.php?t=79840

http://www.mrexcel.com/forum/showthread.php?t=67881


HTH
DK