Changing sheet references without the use of Indirect

m505753

New Member
Joined
Sep 24, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a workbook for each month of the year (9 to date!). Each workbook has a tab for 17 European countries, named in their 2 digit ISO code, e.g. DE for Germany, FR for France and ES for Spain etc. plus a Europe tab which totals up the individual tabs. In each country tab, there is a list of products (about 60 rows) in column D, with some sales data in columns F to I. I want to create a seperate workbook to act a summary file, having a column with the products listed and the 4 columns of sales data next to that for January, then for Feb next to that etc., but have a drop down list, so that you can choose either the country or the Europe total, so that if you select Germany for example, it will pull through the Gemany sales data for each month. I know this will take a while to calculate, as it will need to look for each file / workbook to pull the numbers through, but I am ok with that.

So far, the only way I have manged to do this, is using a vlookup to look up up the product, but using the Indirect formula in the table array piece of the vlookup to link to the cell with the data validation list containing the names of the worksheets. The problem with this, is that each months workbook will need to be open for the Indirect formula to work. Each workbook, is about 10mb-12mb, so not ideal. I've tried using a named range referring to each tab for each workbook, i.e De_Jan for the Germany data in the January workbook, then using Indirect for changing the first part of the name reference, but I get the same issue of the workbooks needing to be open for it to work. i.e. =IFERROR(VLOOKUP($D9,INDIRECT($D$2&"_Jan"),E$1,FALSE),"") where $D9 is my product to look up, $D$2 is my data validation drop down list of countries.
If I type this:
=IFERROR(VLOOKUP($D10,DE_Jan,E$1,FALSE),"")
it works, but it is fixed to Germany.

Is there a way I can solve this without using "Indirect"?
So, in short, is there a way to lookup to another workbook were the sheet reference changes based upon a dropdown seletion without using Indirect?
I am open to the idea of VBA if it can't be done using excel formulae, but I'm not that experience at writing VBA code.

Many thanks in advance.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,492
Office Version
  1. 2010
Platform
  1. Windows
One way you could do it, if it is working with Vlookup is to rewrite the vlookup equation with vba like this:
VBA Code:
Sub temp()
 Country = Cells(2, 4)
 ' =IFERROR(VLOOKUP($D9,INDIRECT($D$2&"_Jan"),E$1,FALSE),"") w
Range("D9:d20").Formula = "=IFERROR(VLOOKUP($D9," & Country & "_Jan" & ",E$1,FALSE)," & Chr(34) & Chr(34) & ")"
 End Sub
You can change the range this is written to, e.g D9:D29 , and you could trigger this macro automatically when ever cell D2 is changed using the worksheet change event, so it would virtually invisible
 
Last edited:

m505753

New Member
Joined
Sep 24, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you offthelip, I'll give it a go.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,946
Members
410,713
Latest member
TaremyLunsil
Top