Excel Reference Question

[sublime1184]

Alright, another Excel question. I have 35 sheets in my workbook. The 35th sheet is a master reference sheet. All of the other 34 sheets have the names of clients in cell A7. I would like to have the master reference sheet display Cell A7 of each sheet into the master reference sheet. I’d prefer it into 34 cells ranging A7 to A41. I know I can go one by one entering each formula like:

=”smith, john”!A7

But is there an easier/faster way?

 

[Corax]

You are trying to call a specific cell from 34 different sheets into different cells on one sheet.. Correct? There isn't an easy whiz bang way to do that all at once. Each cell will have to look exactly as you've described. The only shortcut I can suggest is to hit =, then click the tab of John Smith's sheet, click cell A7 and hit enter. That'll pull that cell address into the formula and eliminate having to type John Smith, Mike Hunt or Amanda Hugenkiss 34 times... Might take 5 minutes to do it all, if you are a slow clicker..

 

[sublime1184]

I usually click on the sheet that way but I've been looking for an easier way so that when all the clients entered in (I've got 35 out of 64 in the file so far) I could just get it automatically. I'm sure there is a custom formula for that but I haven't found it yet...


Ok, how bout this. In the comments, is there a way to link to another sheet in the same book? I know you can hyperlink using cells but I want to add a link to the master reference sheet in comments on certain sheets.

 

[Corax]

There may be another way to do it, but the way I described is what I'd use.

 

[dougall]

There is at least one other way to do it.. you can define the cell as a 3 dimensional array across the worksheets by defining the range of worksheets (along the lines of sheet1:sheet34) and then parsing from there.


Of course, it would be quicker to just manually type the worksheet names in manually than to look in to what function you would use to pull them out. for only 34 worksheets, I'd type them in the old fashioned way, use the consultancy fee you save to buy a line of shots.

 

[sublime1184]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)



Soooo this is the formula, for anyone who cares. It took me a while but I got it now. The formula as it is will only work on the cell so in sheet 1 it will display sheet 1, but if you copy it into sheet 2 it displays sheet 2. If you add the name of the sheet (as I did in Bold), you will be able to insert the name on to a master sheet:

=MID(CELL("filename",'Sheet1'!A1...


...that's the only part you need to add the sheet name to.

Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;

=CELL("filename",A1)