Link data in another speadsheet with text and formula current spreadsheet

[happydays886]

Hey all,

Not sure how else to describe this.

Basically, I'd like to create a summary that pulls information from different spreadsheets into one spreadsheet. Very simple objective.

The complication is, I don't want to write a bunch of formulas to pull the data across multiple spreadsheets, instead, I'd like to do use a reference column and a formula to pull the information that way:

Spreadsheet names: Rus, Afr, Can, etc.
- Data stored in Cell A2 for each spreadsheet

When doing this manually, the formula would be:
Field name------Formula
Russia----------=RUS!A2
Africa-----------=AFR!A2
Canada---------=CAN!A2

However, there is manual work in that; I'd prefer to write a formula then propagate down, something like this:

Summary Spreadsheet (the objective field):
Field name-------Formula
Russia----------=left(3)&"!"A2
Africa-----------=left(3)&"!"A2
Canada---------=left(3)&"!"A2

Problem - this is an invalid formula and I'm not sure if this is possible.

Cheers,

[Neil_]

=Indirect(left(A2,3) & "!A2")

this will only work in the same workbook. Indirect is flakey with linked workbooks.

After editing this 3 times I'm not entirely sure what you're asking

[happydays886]

Thanks a lot for your review and feedback - very much appreciated.

I can see the importance of adding a work-book. See the attachment in OP.

Basically, the 'summary' tab shows the long/manual way of accomplishing this objective.

I was hoping there would be a way to automate this as, if you have 50+ divisions, building a summary model similar to this would take a very, very long time.

[Neil_]

This should be ok as long as you only have 3 streams but lots of countries. The cell references in inverted commas in an Indirect() function don't increment when filling down, you see.

[happydays886]

Thank you Neil, that's perfect.

What's interesting is the use of spaces ([space])in the formula:
=indirect("___"[space]&[space]"!__")

What is this doing to the function?

[Neil_]

Excel automatically adds them for readability. try backspacing them out.

Edit: no it dosen't :/
Well, I added them for readability

[happydays886]

I tried updating this to a global data set and I ran into the same problem - the data wont propagate.

In the formula =Indirect(RUS&"!C6"), "!C6" will not change to "!D6" or "!C7" when autopopulated.