Lookup by SHEET name

[lonewolf9899]

Hello,

Not even sure if this is possible. I have a master Excel spreadsheet with many sheets, each named after a specific division (sheet one is named 12-1, sheet two is named 12-2, etc).

Another spreadsheet lists the # of employees per division.
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=67 height=17>Division</TD><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width=64>Employees</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12-1</TD><TD class=xl24 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>52</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12-2</TD><TD class=xl24 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12-3</TD><TD class=xl24 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12-4</TD><TD class=xl24 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>33</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12-3</TD><TD class=xl24 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>16</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12-8</TD><TD class=xl24 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>17</TD></TR></TBODY></TABLE>


Is there anyway to do some sort of lookup where I can get the employee count listed on the master list, with the # automatically going to the specific sheet? Each division sheet has no data in it (they do not have the division name in it, this info is ONLY on the sheet name).

I.E. Sheet named 12-1 will end up showing a count of 52 employees in cell A1 (any cell, doesnt matter). 12-2 sheet will show 5 and so on...

 

[shg]

You can get the name of the sheet with a formula:

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

Then use that to do a VLOOKUP on the table containing the division names and employees.

 

[Jonmo1]

Assuming your sheet containing the list is named "Master"
And Division is in columnA, with Employees in ColumnB

Select ALL the sheets (except master).
Hold the CTRL key and click on each sheet.
They will all be highlighted.

In A1 put
=VLOOKUP(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),Master!A:B,2,FALSE)
press enter

That should do it.

 

[CharlesChuckieCharles]

=INDIRECT(A1 & "!A1")

where A1 = your sheet reference and "!A1" is your count of on the sheet referenced by A1

if you want refernce a sheet based on a name INDIRECT is the function

 

[lonewolf9899]

JonMo1 ; thats exactly what I needed! I grouped all the sheets together, pasted the formula so it applied to all of them simultaneously, and bam! Worked like a charm. Thank you so much!!!!!!!!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[lonewolf9899]

Quick followup question.

The formula you provided to me, listed below alongside the link to original post, works great IF my Spreadsheet/Sheet housing all the info is named Master. What happens if I want this spreadsheet to be named Master.xls but have multiple pieces of info saved on different sheets;

I.E., Master.xls with sheets named EECont01. I tried to alter the formula but keep getting a #Value error.


ORIGINAL FORMULA;

=VLOOKUP(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),Master!A:B,2,FALSE)


ALTERED FORMULA;

=VLOOKUP(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),[Master.xls]EECont01!A:B,2,FALSE)

 

[CharlesChuckieCharles]

Indirect

 

[Jonmo1]

Looks like it should work,

If the book Master.xls is closed, you have to include the path...

=VLOOKUP(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),'C:\path\folder\[Master.xls]EECont01'!A:B,2,FALSE)


Also, the book holding the formula must be saved....

The Cell function will not work on a book that has not been saved (i.e. a New Book).

 

[lonewolf9899]

Wow. I cant believe it was that simple. I was using a dummy excel spreadsheet to test it, so I wouldnt inadvertantly save over the real spreadsheet. Once I saved the dummy, it worked. Thanks again! and for teaching me to post my followups IN the forum