auto change of table array in the vlookup

[iplayball]

hi guys,

I was wondering if anyone could help. I got a vlookup formula, and I'd like to change the bolded part of the table array
=VLOOKUP(AD4,'[C1 1st Shift.xls]7.5.12'!$A$3:$G$20,7,FALSE)

In a nut shell here's what im trying to do. I have a monthly workbook with 30 or 31 worksheets(depending on the month). Each sheet in this workbook has the name of each day of the month(ex.7.15.12). The vlookup works awesome, but when i come to a new month(say august) i have to manually update all the vlookups for the month of august. So i was thinking if i put the new date(say 8.15.12) in another cell, and have the vlookup update the date part to 8.15.12(VLOOKUP(AD4,'[C1 1st Shift.xls]8.5.12'!$A$3:$G$20,7,FALSE)

See pic for more clarification.


thanks everyone.

[TMS]

Untested, but it would be something like:


=VLOOKUP(AD4,INDIRECT("'[C1 1st Shift.xls]" & AC7 & "'!$A$3:$G$20",7,FALSE)

Well, you'd use INDIRECT anyway


Regards, TMS

[iplayball]

hey TMShucks thanks for the fast response.
I tried your formula but i got the following error: "You've entered too many arguments for this function"

[vikas.bhandari]

you probably have not entered the formula

Please Login or Register  to view this content.

correctly. The error means that you have supplied extra arguments to either Indirect or Vlookup formula. Please share the formula you have entered, and we will fix that for you.

Thanks,
Vikas B

[TMS]

Mmmm, I said untested (and late in the night here)

I think an extra bracket is needed:

=VLOOKUP(AD4,INDIRECT("'[C1 1st Shift.xls]" & AC7 & "'!$A$3:$G$20"),7,FALSE)


Regards, TMS

[iplayball]

TMShucks,

Thanks for staying with me on this one. I tried it again with the extra bracket and got an
#REF! this time. Getting closer though, appreciate the help on this.

[TMS]

You probably need to use Evaluate Formula to see what it sets up and, therefore, when/where it goes wrong.

A sample workbook would be more helpful than a picture of your workbook if you want more practical help.

Regards, TMS

[iplayball]

good idea. here it is.
http://dl.dropbox.com/u/489116/test.xls

[TMS]

Maybe:

=VLOOKUP(B4,INDIRECT("'C:\Users\...\Downloads\[C1 1st Shift.xls]" & TEXT(A7,"m.d.yy") & "'!$A$3:$G$20"),7,FALSE)

I still can't test this as I don't have the files you are linking to. However, the original formula was picking up the date in the cell ... which is simple a number ... and this version formats it as your file name structure. However, I suspect the file will need to be open, though I can't test this.

Regards, TMS

[Paul]

Correct, TMS. Using INDIRECT requires that the other file be open, else it will return a #REF error. There are add-ins that have custom functions (e.g. INDIRECT.EXT) that can get around this limitation.

[TMS]

@Paul: thanks. I though that was the case but it's not something I tend to do. Cheers, TMS

[iplayball]

Maybe:

=VLOOKUP(B4,INDIRECT("'C:\Users\Trevor\Downloads\[C1 1st Shift.xls]" & TEXT(A7,"m.d.yy") & "'!$A$3:$G$20"),7,FALSE)

I still can't test this as I don't have the files you are linking to. However, the original formula was picking up the date in the cell ... which is simple a number ... and this version formats it as your file name structure. However, I suspect the file will need to be open, though I can't test this.

Regards, TMS

Thanks TMShucks and Paul.
Here is the other file:
http://dl.dropbox.com/u/489116/C1%201st%20Shift.xls

thanks again for your help on this.

[TMS]

Cell B10: =VLOOKUP(B$4,INDIRECT("'[C1 1st Shift.xls]" & TEXT($A10,"m.d.yy") & "'!$A$3:$G$20"),7,FALSE)

Copy across row 10. Alternate columns will return #N/A because the 2nd Shift workbook isn't open.

Looks as though you'll need a lot of workbooks to be open for this to work ... one for each day of the month.


Regards, TMS

[TMS]

Thanks for the rep. Judging by your comment,

wow perfect! thanks so much, it worked!!!

, I'm guessing this has answered your question.

Please can you mark the thread as solved.

Regards, TMS

[iplayball]

Thanks again so much, this will really help us save so much time