Dynamic Sheet using Drop Down List & INDIRECT using External File

[sea_captain]

Goal: Use a Drop Down list to Change Data that is linked to another file.

I have 2 files (file 1: 'SOURCE', and file 2: "OUTPUT") - the "Source" file feeds/links to the "Output" file (linking). The Output file is an Income Statement.
I want to use a drop down list to change the data in the income statement by Region/Service Line.
For example, i have tabs in the "Source" file named "US", "East", "Central" and "West". My dropdown list is US, East, Central and West (this is an example, i have a lot more 'tabs'). I want to use the dropdown to change the data from US to any other option in the list.

If everything was in the same file (source and output), i could easily do this using the INDIRECT function. FYI - All P&L's are UNIFORM - meaning Revenue is in the same Cell for each tab etc.

If dropdown list is in cell C5 and Revenue data is in Cell BL5 (in all tabs), i could use INDIRECT($C$5&"!BL5") to change the data.
The problem i'm having is the data is in another file and i'm not sure how to write it, because changing the "external link" cell, gives an error (for obvious reasons)

Any ideas? OR any other ideas using a drop down to change the P&L data.
Thanks - SC

 

[Jeffrey Mahoney]

INDIRECT will not work for external sources. To fix the issue, you can recreate tables from other workbooks in your current workbook using links. Then use INDIRECT to reference those tables.

Am I reading this right? You want to change values in another workbook or in your current workbook?

 

[felixstraube]

Assuming that you Source filename is Source.xlsx, and you have sheets Sheet1 and Sheet2 for example

Source.xlsx
	A
1	Data in Sheet1
Sheet1

Source.xlsx
	A
1	Data In sheet2
Sheet2

you could do this (dropdown in A1):

Output.xlsx
	A	B
1	Sheet1	Data in Sheet1
Sheet1

Cell Formulas
Range	Formula
B1	B1	=INDIRECT("'[Source.xlsx]"&A1&"'!$A$1")

Cells with Data Validation
Cell	Allow	Criteria
A1	List	Sheet1, Sheet2

For this to work you will have to open the source file

 

[Jeffrey Mahoney]

Let me rephrase my statement: INDIRECT won't work for external workbooks that are not open at the same time by you.

 

[sea_captain]

Thanks. I added a bunch of hidden tabs withe the data needed. So the source data is now in the Output file. Not what I wanted , but I will give your suggestion a go. Thanks again. -sc

 

[smozgur]

Thanks. I added a bunch of hidden tabs withe the data needed. So the source data is now in the Output file. Not what I wanted , but I will give your suggestion a go. Thanks again. -sc

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.