INDEX MATCH with multiple spreadsheets

milanvi1 

In the attached revisions to your files are three examples of how INDIRECT could be used.

For Project 1, I chose the simplest.

=INDIRECT($A$1&A5)

Here is an image of the screen with that formula showing in cell C5; the formula is just copied down the rest of column C. $A$1 is the cell containing the reference to the other file and the appropriate tab; A5 refers to the cell in that tab. INDIRECT contains those two references and the concatenation operator (&) which puts them together as a complete reference. 

For project 2 I use INDIRECT within a VLOOKUP, again in cell C5 and copied down.

=VLOOKUP(B5,INDIRECT($A$1),2,0)

In this case, cell $A$1 contains a reference to the file and tab, but also a reference to a "named range" in File B. I assigned a name--"Proj2Bud"--to the range of cells that contains the account number and associated cash balances. So here's a screen capture of what that looks like.

 For Project 3 I used INDEX and MATCH and nested INDIRECT in each, using two different cells as references, one in connection with INDEX and the other in connection with MATCH. This is the more complicated, obviously. And this uses two named ranges, one for account numbers, the other for the cash balances.

=INDEX(INDIRECT($A$2),MATCH(B5,INDIRECT($A$1),0))

It looks like this: 

I'm attaching revised versions of both your files, so you can play around with the various methods. I want to add a couple of "however" though... this is meant to show how INDIRECT works, and might work for you.

HOWEVER #1, the source file for these--your "Excel File B"--is conveniently laid out so that B9 through B14 contain the balances in the associated account numbers. This makes it easy for each of the different approaches to work. In your real world is that the case?

HOWEVER #2, and it's a big however, from the description you've given, I wonder if there aren't other changes I'd want to make if I were in your shoes, or if we were sitting down face to face. The fact that you have 50 spreadsheets for different projects raises flags in my mind. That may be legitimate--there may indeed be compelling reasons to have one sheet per project--but it may also be just because it's easier for humans to comprehend. When working with a database (in this case, that's what we're doing), Excel can do wonders if data regarding all comparable items (e.g., projects) are stored in a single table, from which it can extract project specific data quite readily. By "manually" separating them into individual sheets--unless they're distinct in multiple ways, using categorically different types of data--we sometimes inadvertently (for all the best motives) make it harder to get summary inclusive reports.

So I offer that as backdrop to these starter questions:

1. Why do you have one sheet per project?
2. How different are they?
3. Could all the data on each project be combined into a single database, with a single column denoting which project a given item represents?
4. Would it be possible in your real world files to have both workbooks combined? They'd still be able to use INDIRECT to refer to different tabs, but would not have to go through the potential volatile combination of incorporating a different filename as part of that connection.