Hello all,

Using VLOOKUP in my base workbook, I am referencing several different sheets in an external workbook that must be a closed file (therefore, I don't think the INDIRECT function will help me); what I would like to do is vary the sheet that is referenced from the external workbook via an input in my base workbook. This is my basic formula:

=VLOOKUP($B$19, 'R:[Parts Reference v1.0.xlsx]CompanyName'!A1:J1505, 3, FALSE)

I would like to vary my "CompanyName" via a drop down list in cell B1 in my base workbook so that at first glance one would think the new formula would look something like this:

=VLOOKUP($B$19, 'R:[Parts Reference v1.0.xlsx]B1'!A1:J1505, 3, FALSE)

However, the problem is the above formula simply looks for a sheet B1 in the external workbook (which does not exist).

Now I am trying a bit different approach using the OFFSET function nested inside of my VLOOKUP to try and reference the data in cell B1, but I am beginning to think this may be a dead end as well.

Any suggestions would be greatly appreciated as this could save me from a staggering number of IF statements.

Thanks,

Andrew