Changing Formula
 

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?

Changing Formula
 

One way
insertnamedefinename it togglecolumnin the refers to box
=if($e$1=1,Detail!$j$2:$j$46962,Detail!$K$2:$K$469 62)
then change cell e1 to set the range

--
Don Guillett
SalesAid Software

"Secret Squirrel" wrote in
message ...
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?

Changing Formula
 

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

1. What exactly do I put in cell e1?

"Don Guillett" wrote:


One way
insertnamedefinename it togglecolumnin the refers to box
=if($e$1=1,Detail!$j$2:$j$46962,Detail!$K$2:$K$469 62)
then change cell e1 to set the range

--
Don Guillett
SalesAid Software

"Secret Squirrel" wrote in
message ...
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?

Changing Formula
 

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

"Don Guillett" wrote:


One way
insertnamedefinename it togglecolumnin the refers to box
=if($e$1=1,Detail!$j$2:$j$46962,Detail!$K$2:$K$469 62)
then change cell e1 to set the range

--
Don Guillett
SalesAid Software

"Secret Squirrel" wrote in
message ...
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?

Changing Formula
 

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

--
Don Guillett
SalesAid Software

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

"Don Guillett" wrote:


One way
insertnamedefinename it togglecolumnin the refers to box
=if($e$1=1,Detail!$j$2:$j$46962,Detail!$K$2:$K$469 62)
then change cell e1 to set the range

--
Don Guillett
SalesAid Software

"Secret Squirrel" wrote in
message ...
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?