Changing Formula

[Guest]

I have two worksheets that I'm working with. One is my "Detail" and the other
is my "Summary". On the summary worksheet I'm calculating on time delivery.
On the detail worksheet I have 3 dates, one for original due date, one for
last promise date, and one for the actual receipt date. On my summary
worksheet I'm currently calculating the difference between the last promise
date and the receipt date. What I want to do is be able to change the formula
from the last promise date to the original date by simply adding a cell where
I can enter a specific word and have it change the formula. Below is a copy
of the formula I'm using.

=SUMPRODUCT(--(MONTH(Detail!$I$2:$I$46962)=MONTH($A8)),--(YEAR(Detail!$I$2:$I$46962)=YEAR($A8)),--(Detail!$B$2:$B$46962=Summary!$B$3),--(Detail!$K$2:$K$46962<2))

In the formula the part "--(Detail!$K$2:$K$46962<2)" is where I want to
change it from column "K" to column "J". Is there any easy way I can toggle
this formula back and forth without having to modify the formula directly?

 

[Don Guillett]

One way
insert>name>define>name it togglecolumn>in the refers to box
=if($e$1=1,Detail!$j$2:$j$46962,Detail!$K$2:$K$46962)
then change cell e1 to set the range

 

[Guest]

Hi Don,
I think I understand what you mean but I have a question.

1. What exactly do I put in cell e1?

 

[Guest]

Also, How do I modify my formula to incorporate this defined name? That's the
part I'm still unclear about.

 

[Don Guillett]

e1 should have 1 to use col J.

Untested

=SUMPRODUCT(--(MONTH(Detail!$I$2:$I$46962)=MONTH($A8)),--(YEAR(Detail!$I$2:$I$46962)=YEAR($A8)),--(Detail!$B$2:$B$46962=Summary!$B$3),--(togglecom<2))