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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,603
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.
 

Forum statistics

Threads
1,172,096
Messages
5,879,133
Members
433,394
Latest member
Stan Roberts

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top