asked on

Excel 2007 - how to insert cell value into a formula

I've got the following formula in an Excel 2007. I don't know VBA.

=VLOOKUP(F43,[FileName.xls]C16!$H:$U,14,FALSE)

I'd like for the C16 part of the formula above to be a variable, so that a cell a couple of columns to the left, C43, dictates the worksheet within the file FileName.xls containing the table array for this VLOOKUP.

Is this possible? If so, please teach me how.

StephenJR

Like this (assuming it's C43 in the same sheet):

Range("A1").Formula = "=VLOOKUP(F43,[FileName.xls]" & Range("C43").Value & "!$H:$U,14,FALSE)"

SolidGold

ASKER

Thanks for the quick reply StephenJR!

Can you dumb it down for me a tad?

Should I be able to copy/paste what you've provided into my formula?
=VLOOKUP(F43,[FileName.xls]" & Range("C43").Value & "!$H:$U,14,FALSE)

I'm unfamiliar with some of the ideas you're using in there, like Range, Formula and Value.

StephenJR

Apologies, I misread and thought you wanted VBA. This formula should work in whatever cell you want:

=VLOOKUP(A2,INDIRECT(C43 & "!$H:$U"),14,FALSE)

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SolidGold

ASKER

Still struggling, StephenJR...

Where does your formula define the the workbook where the table array is located? I was using [FileName.xls] in my example.

The VLOOKUP table array is in a separate workbook file, in case I didn't make that clear.

StephenJR

So what exactly is in C43? I was just following your example, you can use any workbook name.

SolidGold

ASKER

Apologies, Stephen, I posted my comment about defining the workbook before I saw your follow up comment that included [FileName].xls.

Your formula works. Thank you!

StephenJR

My pleasure.