CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vlookup
Cheese and Chill
Cheese and Chill

Reputation: 33

Excel VLOOKUP with variable as sheet reference

I currently have this kind of Function :

=VLOOKUP(A6&Values!$A$6,'pathToMyFile]Sheet1'!$A:$I,5,FALSE)

And i'd like to create a kind of variable in another sheet(as for 'Values!$A$6') but for my path to file like : =VLOOKUP(A6&Values!$A$6,'Values!$A$8'!$A:$I,5,FALSE)

The aim is that for futher KPI calculation the only parameter to change is the name of the document in a single cell.

any advice or tips or even solution?

Upvotes: 2

Views: 857

Answers (1)

ptphdev
ptphdev

Reputation: 44

Suppose you have 3 sheets:

  • One overview sheet (named: Overview)
  • Two sheets where you have the discount percentages of different products (Product A, Product B, ...) split by month (named: March and April)

So for example, sheet=March includes:

  • A1: Product / B1: Discount
  • A2: Product A / B2: 10%
  • A3: Product B / B3: 12%
  • ...

We can reference in the sheet 'Overview' to the other sheets (i.e. March and April) using the following VLOOKUP formula:

=VLOOKUP(A5,INDIRECT($B$2&"!A:B"),2,FALSE)

... where A5 is the product (Product A, Product B, ...) you are looking for, $B$2 is the dynamic reference cell where you can (e.g. using a dropdown-list) select a month (March, April) and 2 indicates the second column (i.e. the 'Discount' column) and FALSE to 'Exact match'.

Note : The function INDIRECT() only works (1) within the workbook or (2) with workbooks that are open during the calculation time. If the external workbook is closed, it will result in a #REF! error.

Hope this helps!

Upvotes: 0

Related Questions