Possible to use a List as a Sheet Name Reference?

[mark5767]

It like there must be an easier way to do this. In the following example the costs for each FGI SKU are on detail cost build sheets each named simply 600001, 600002, etc.

To add a summary I simply list all the SKU's on a summary worksheet and reference each detail sheet. The problem is with large numbers of SKU's it gets clumsy having to create a reference for each sheet.

Is there a way to have the formula in the cost column reference the detail sheet based on the value in the FGI SKU column?


<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64>FGI SKU</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64>Cost</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl24 height=17 x:num>600001</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl23 x:num="11.34">11.34 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl24 height=17 x:num>600002</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl23 x:num="11.34">11.34 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl24 height=17 x:num>600003</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl23 x:num="11.34">11.34 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl24 height=17 x:num>600004</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl23 x:num="11.34">11.34 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl24 height=17 x:num>600005</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl23 x:num="11.34">11.34 </TD></TR></TBODY></TABLE>

So to reference the cost for the 600002 SKU instead of this...

=+'600002'!$E$37

I would like this...

=+'value in cell with 600002 in it'!$E$7

Hopefully that makes sense? Any help greatly appreciated, thank you!

 

[sheetspread]

Use indirect

 

[VoG]

Try

=INDIRECT("'"&E7&"'!E37")

 

[mark5767]

That works quite well, thank you people!

I have not used INDIRECT before, but it looks pretty useful for this application (and maybe a few others!). FYI for others new to INDIRECT here's the description from Excel 2000 help...

INDIRECT
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

So for evaluating the contents of my list of SKU numbers and changing the reference based on the values in the list this is perfect.

Very handy gentlemen, thank again!