Indirect worksheet lookup in long formula

[pdberger]

Good evening --

I have 3 years of reference data stored in worksheets labeled 2008, 2009,
2010 respectively. I would like to allow the user to select a year in cell
F2, and have the formula select the right page for these sumproduct formulas.
That is, everywhere the formula selects page '2008', I'd like it to select
the worksheet based on what's in cell F2. Here's the formula:

=IF(OR(ISBLANK($B12),$B12="TC",$B12=26),SUMPRODUCT(--('2008'!$A$1:$A$20000=$A12),--('2008'!$B$1:$B$20000=$B12),'2008'!$D$1:$D$20000),VLOOKUP($B12,Modifiers,3,FALSE)*SUMPRODUCT(--('2008'!$A$1:$A$20000=$A12),--('2008'!$B$1:$B$20000=""),'2008'!$D$1:$D$20000))

Thanks in advance. I've spent the better part of a day trying to get the
single and double parentheses correct...

Peter

 

[Don Guillett]

You can use an INDIRECT formula or simply use Edit>replace '2008' with
'2009'

 

[JBeaucaire]

INDIRECT() is a volatile function, as is SUMPRODUCT() I believe. Once you
get this working, EVERY CELL with this formula in it will recalculate every
time you change anything anywhere in your workbook. So, I hope you aren't
planning on putting a lot of these formulas in your workbook.

=IF(OR(ISBLANK($B12), $B12="TC", $B12=26), SUMPRODUCT(--(INDIRECT("'" & F2 &
"'!$A$1:$A$20000")=$A12), --(INDIRECT("'" & F2 & "'!$B$1:$B$20000")=$B12),
INDIRECT("'" & F2 & "'!$D$1:$D$20000")), VLOOKUP($B12,Modifiers,3,FALSE) *
SUMPRODUCT(--(INDIRECT("'" & F2 & "'!$A$1:$A$20000")=$A12), --(INDIRECT("'" &
F2 & "'!$B$1:$B$20000")=""), INDIRECT("'" & F2 & "'!$D$1:$D$20000")))

 

[T. Valko]

INDIRECT() is a volatile function, as is SUMPRODUCT() I believe.

SUMPRODUCT is not volatile.

We can eliminate the use of the volatile INDIRECT and at the same time
greatly reduce the length of the formula by using some defined names.

Insert>Name>Define
Name: Sh2008
Refers to: ='2008'!$A$1:$D$20000

Name: Sh2009
Refers to: ='2009'!$A$1:$D$20000

Name: Sh2010
Refers to: ='2010'!$A$1:$D$20000

Name: Sheet
Refers to:
=CHOOSE(MATCH(Sheet1!$F$2,{2008,2009,2010},0),Sh2008,Sh2009,Sh2010)

Name: Calc
Refers to:

=SUMPRODUCT(--(INDEX(Sheet,,1)=Sheet1!$A$12), --(INDEX(Sheet,,2)=Sheet1!$B$12),
INDEX(Sheet,,4))

Then the formula becomes:

=IF(OR($B12={"","TC",26}),Calc,VLOOKUP($B12,Modifiers,3,0)*Calc)