Indirect worksheet lookup in long formula
 

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

Indirect worksheet lookup in long formula
 

You can use an INDIRECT formula or simply use Editreplace '2008' with
'2009'

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pdberger" wrote in message
...
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

Indirect worksheet lookup in long formula
 

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")))


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"pdberger" wrote:

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

Indirect worksheet lookup in long formula
 

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.

InsertNameDefine
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),Sh20 08,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,Modifi ers,3,0)*Calc)

--
Biff
Microsoft Excel MVP


"JBeaucaire" wrote in message
...
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")))


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"pdberger" wrote:

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