Cell Reference when the worksheet names change

[PLD60]

I need a summary workbook of all of the worksheets of a different Workbook. It would pull values from 4 fixed cells, but the number of Worksheets and names of the sheets change.

Is there a way to do this?

[humdingaling]

use a indirect formula
http://www.contextures.com/xlFunctions05.html

[PLD60]

I don't think indirect will work. The problem is that users add and delete worksheets and the name of the worksheet is automatically changed and sorted. Need something that will look up the worksheet names and then pull the values from (A1), (J1), (K1) in those worksheets.

Output would be if possible in a separate workbook or at least in a separate worksheet that would have header row, then rows 2-x (x could be as many as 30) where each row would be values from a different worksheet.

Thanks for any help.

[FDibbins]

If you are doing this in 2 different files/workbooks, then INDIRECT() would only work if both files were open anyway.

I have a way to ID all sheets in a file (added/deleted/changed etc). Would you consider having a summary sheet in teh "workers" file, then just referencinfg that from teh "master" file?

[PLD60]

Fdibbins,

Would you consider having a summary sheet in teh "workers" file, then just referencinfg that from teh "master" file?

That would be fine.

[FDibbins]

Sorry for the delay, this is what I had in mind (well, something like thuis)

Sheet1 of the attached is the summary sheet. It pulls in all the sheet names in the WB, and you can then use that to extract the sheet data.

Once you have that in 1 sheet, in a structured manner, you can reference that (be that file open or closed) from a 2n WB

Another (manual) way to get the sheet names...

Quick list method (from TMS)
1 Open VBA editor (Alt F11)
2 hit CTRL-G to open the Immediate Window
3 paste this...for i=1 to worksheets.Count: ?sheets(i).name: next
4 Copy/paste resulting list

[PLD60]

Fdibbns,

Thanks so much for your help. The attached file works Ok. When I rename a sheet it doesn't change until I close and reload the workbook. If I add a sheet nothing happens even after I added the code on Sheet1 down the rows until I highlight the code and then enter.

Is there a way for this to happen automatically?