Reference to SheetName in Formula

[jim may]

I have so far the below. I wish to enter a formula in Cell C7 on Sheet LongView, that will pull in the $$'s from the Sheet Spice Mgr Input Cell E4's Amt; So the C7 formula would use the Mid() function to get the "Spice" frow the "01 - Spice" in Cell B7 and concatenate the " Mgr Input" to reference the ='Spice Mgr Input'!E4;
Can someone assist? Tks, Jim

Excel Workbook
	B	C	D	E
6		5/8/2011	5/15/2011	5/22/2011
7	01 - Spice	625.04	586.85	612.14
8	02 - Produce
9	03 - Cheese
LongView

Excel 2007

Cell Formulas
Range	Formula
C6	='Spice Mgr Input'!D4
C7	='Spice Mgr Input'!E4
D6	='Spice Mgr Input'!D5
D7	='Spice Mgr Input'!E5
E6	='Spice Mgr Input'!D6
E7	='Spice Mgr Input'!E6

Excel Workbook
	C	D	E
2	01 - Spice	** THIS YEAR **
3		Week Ended	Actual Sales
4		5/8/2011	625.04
5		5/15/2011	586.85
6		5/22/2011	612.14
7		5/29/2011	520.47
Spice Mgr Input

Excel 2007

Cell Formulas
Range	Formula
D4	=D5-7
D5	=D6-7
D6	=D7-7
D7	=D10-7
E4	=SUMPRODUCT(--('Current Year GL'!$A$7:$A$3532=LEFT($C$2,2)),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")>=TEXT(D4-6,"mm-dd")),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")<=TEXT(D4,"mm-dd")),('Current Year GL'!$I$7:$I$3532))
E5	=SUMPRODUCT(--('Current Year GL'!$A$7:$A$3532=LEFT($C$2,2)),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")>=TEXT(D5-6,"mm-dd")),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")<=TEXT(D5,"mm-dd")),('Current Year GL'!$I$7:$I$3532))
E6	=SUMPRODUCT(--('Current Year GL'!$A$7:$A$3532=LEFT($C$2,2)),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")>=TEXT(D6-6,"mm-dd")),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")<=TEXT(D6,"mm-dd")),('Current Year GL'!$I$7:$I$3532))
E7	=SUMPRODUCT(--('Current Year GL'!$A$7:$A$3532=LEFT($C$2,2)),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")>=TEXT(D7-6,"mm-dd")),--(TEXT('Current Year GL'!$D$7:$D$3532,"mm-dd")<=TEXT(D7,"mm-dd")),('Current Year GL'!$I$7:$I$3532))

 

[jim may]

After 77 attempts (while waiting...) I got it!!!!

=INDIRECT("'"&MID(B7,6,LEN(B7)-5)&" Mgr Input"&"'!"&"E4")