I have two spreadsheets that I need this for, so I don't think this problem
is unique. I know how to get data from another worksheet. But what if the
name of the sheet that I want to pull it from is dependent on something on my
current worksheet. For example I use this formula right now which works fine.

='[anotherfile.xls]Jeff'!$A$1

But I want to be able to pull from different sheets based on values in my
existing sheet. So basically I want to be able to replace 'Jeff' in the
above formula with something like "B2" where B2 could be "Jeff", "Jay",
"Joe", etc. Note that B2 is located in the current worksheet, not in
[otherfile.xls]Jeff.

If I use ='[anotherfile.xls]B2'!$A$1 I get an error. Hopefully this is clear.

Thanks.