Changing sheet references without the use of Indirect

[m505753]

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.

 

[offthelip]

One way you could do it, if it is working with Vlookup is to rewrite the vlookup equation with vba like this:

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

 

[m505753]

Thank you offthelip, I'll give it a go.