Referencing Text Cell within Formula

[jim may]

I'm vaguely familiar with the INDIRECT() formula, but not enough to solve my problem.

In my Worksheet Named MAIN I currently have the formula (WHICH WORKS!!):

=SUM('Aug 2017'!K25:K28)

I was to modify the above so that I can reference in the 'Aug 2017' portion using another reference.

In my MAIN sheet Cell C1 I have the general number 8 << representjng my calendar Month #.

I have Another Worksheet Named LOOKUPDATA set up as follows:

Excel 2010

	A	B	C	D	E	F	G
SS
Month	Second	WS Names

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]

[TD="align: center"] Nancy's Pay Checks[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"] Wednesday[/TD]
[TD="align: center"] First[/TD]

[TD="align: center"] Third[/TD]

[TD="align: center"] MonthEnd[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]1/25/2017[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]1/20/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Jan 2017[/TD]
[TD="align: right"]1/31/2017[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]2/22/2017[/TD]
[TD="align: right"]2/3/2017[/TD]
[TD="align: right"]2/17/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Feb 2017[/TD]
[TD="align: right"]2/28/2017[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]3/22/2017[/TD]
[TD="align: right"]3/3/2017[/TD]
[TD="align: right"]3/17/2017[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD="align: center"]Mar 2017[/TD]
[TD="align: right"]3/31/2017[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]4/26/2017[/TD]
[TD="align: right"]4/14/2017[/TD]
[TD="align: right"]4/28/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Apr 2017[/TD]
[TD="align: right"]4/30/2017[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]5/24/2017[/TD]
[TD="align: right"]5/12/2017[/TD]
[TD="align: right"]5/26/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]May 2017[/TD]
[TD="align: right"]5/31/2017[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]6/28/2017[/TD]
[TD="align: right"]6/9/2017[/TD]
[TD="align: right"]6/23/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Jun 2017[/TD]
[TD="align: right"]6/30/2017[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]7/26/2017[/TD]
[TD="align: right"]7/7/2017[/TD]
[TD="align: right"]7/21/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Jly 2017[/TD]
[TD="align: right"]7/31/2017[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"] 8 [/TD]
[TD="align: right"]8/23/2017[/TD]
[TD="align: right"]8/4/2017[/TD]
[TD="align: right"]8/18/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"] Aug 2017 [/TD]
[TD="align: right"]8/31/2017[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]9/27/2017[/TD]
[TD="align: right"]9/1/2017[/TD]
[TD="align: right"]9/15/2017[/TD]
[TD="align: right"]9/29/2017[/TD]
[TD="align: center"]Sep 2017[/TD]
[TD="align: right"]9/30/2017[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]10/25/2017[/TD]
[TD="align: right"]10/13/2017[/TD]
[TD="align: right"]10/27/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Oct 2017[/TD]
[TD="align: right"]10/31/2017[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]11/22/2017[/TD]
[TD="align: right"]11/10/2017[/TD]
[TD="align: right"]11/24/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Nov 2017[/TD]
[TD="align: right"]11/30/2017[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]12/27/2017[/TD]
[TD="align: right"]12/8/2017[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Dec 2017[/TD]
[TD="align: right"]12/31/2017[/TD]

</tbody>

LookUpData

​

So I'm hoping in my original formula to reference in the value in Cell F12 above which is Text - using the
VLOOKUP() and the Indirect().

Can someone assist?

Thanks in Advance.

Jim

 

[RoryA]

I think you mean:

=SUM(INDIRECT("'"&VLOOKUP(C1,LOOKUPDATA!A:F,6,FALSE)&"'!K25:K28"))

 

[Jonmo1]

Or

=SUM(INDIRECT("'"&INDEX(LOOKUPDATA!F:F,C1)&"'!K25:K28"))

Or if Current Year can be assumed

=SUM(INDIRECT("'"&TEXT(C1&"/1","mmm yyyy")&"'!K25:K28"))

 

[RoryA]

The lookup table doesn't start in row 1, so you'll need an offset amount added to get the correct row if you just use C1 directly.

 

[Jonmo1]

Good point, that would have to be

=SUM(INDIRECT("'"&INDEX(LOOKUPDATA!F$5:F$16,C1)&"'!K25:K28"))

 

[jim may]

Thanks Guys!! Got it working -- Jim