need Excel Guru's help...

[ScreaminPain]

Ok, guys, I'm truly baffled and frustrated....

My database has 30 worksheets, all consisting of team names...."ATL", "ARI", BAL, etc.

On a Master sheet, I want to reference a particular sheet via a team name, and import a specific cells data. I'm familiar with match, index and lookup functions, but none of those functions will find a worksheet, then a particular cell on that sheet. I'm told I need to use the INDIRECT function, but the syntax eludes me.

As an example: I want to match a cell containing "ATL" (found on the master), with the appropriate worksheet (in the same workbook) titled "ATL". Then import data from that sheet into a cell on the master sheet. All the teams worksheets have the data in the same place ($D$55).

For many of you guys, this may be an easy task, but any help would be greatly appreciated....

[thompsontw]

To return the tab name try this formula:
=MID(CELL("Filename",I7),SEARCH("]",CELL("Filename",A1),1)+1,100)

This along with another formula may get you want you want.

[ScreaminPain]

To return the tab name try this formula:
=MID(CELL("Filename",I7),SEARCH("]",CELL("Filename",A1),1)+1,100)

This along with another formula may get you want you want.

Interestingly enough, that formula returns the tab name of the MASTER sheet.....I'll continue to "work with it" to see if I can add something that makes sense....

Thanks for the reply..

[Pot luck]

If the text "ATL" is in cell A1 of your master sheet and you want to reference cell D55 in the ATL sheet, use:
=INDIRECT(A1&"!"&"D55")

[ScreaminPain]

If the text "ATL" is in cell A1 of your master sheet and you want to reference cell D55 in the ATL sheet, use:
=INDIRECT(A1&"!"&"D55")

YES! that works perfectly....

Using this formula for future reference, can I assume the portion "!" is the part that directs a search of all the worksheets for the match A1 and returns the value in D55?

Thanks so much for your reply, Pot luck, I've agonized over this for too long....

[Pot luck]

So to reference cell D55 on sheet ATL you write =ATL!D55 (ie =WorkSheet!Cell). The INDIRECT function lets you make up this bit of text indirectly by combining text, enclosed in quotation marks, and the content of cells, not enclosed in quotation marks, suck together using the '&' symbol.

[ScreaminPain]

I see, thank your for your time....

[Pot luck]

no problem

[dr_wolf]

Can be done through VBA and you don't need to load excel with a lot of formulas.