Excel 2007 : Excel - Reference data from another worksheet without using worksheet name in formula

I have the following formula, and it works great...but when my boss
adds a new Job Number to the Summary tab, he will have to edit the
formula to reference the correct worksheet. I was wondering if there
was a way to change the worksheet reference by typing into a specific
cell on the current worksheet?

=VLOOKUP($D8,'GT206 3.1.1'!$B$4:$H$31,2,FALSE)

D8 is the employee name
GT206 3.1.1 is the JN worksheet
B$4:H31 are the columns....5 columns for each week of month
2 is the column return for the week needed

I want to replace'GT206 3.1.1'! with some reference on the worksheet
rather than hardcoded in the formula

I tried using this forumla, but keep getting a #REF error...
=VLOOKUP($D8,(INDIRECT(D4&"!b6:h300")),2,FALSE)

Let me know if this doesn't make any sense!

thanks!

=VLOOKUP($D8,INDIRECT("'"&D4&"'!b6:h300"),2,FALSE)
ought to work. You require the single quotes around the sheet name if it contains spaces (or looks like a date).

If you are getting a #REF error then the worksheet name does not match an actual worksheet. (the range address will not be capitalised as it is just text)

My pleasure.

Please do not forget to mark the thread Solved (see FAQ link at top of the page for instructions).