Can you freeze a result in a cell?
All 5 messages in topic - view as tree
jacob Apr 19, 11:34 am show options

Newsgroups: microsoft.public.excel.misc
From: "jacob" - Find messages by this author
Date: 19 Apr 2005 11:34:08 -0700
Local: Tues,Apr 19 2005 11:34 am
Subject: Can you freeze a result in a cell?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Here's the dilemma:


I have a list validation in H7. You can choose from 6 different text
values.
Below, in H8, I would like to respond "true" (or yes) if a certain 1 of

the 6 text values is EVER selected, and then not be changed by anyone
else.


For instance, if the "flagged" value is "blue", and a user ever selects

blue from the drop-down list in H7, can I create an argument that
populates H8 with "True"? And keep it as true forever, even if you go
and change H7 to another value.


Hope this makes sense. Thanks sirs and madams!


Jacob


Reply


Biff Apr 19, 12:05 pm show options

Newsgroups: microsoft.public.excel.misc
From: "Biff" - Find messages by this author
Date: Tue, 19 Apr 2005 15:05:56 -0400
Local: Tues,Apr 19 2005 12:05 pm
Subject: Can you freeze a result in a cell?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Hi!


You *COULD* do this using an intentional circular reference but you
should
wait until someone posts a VBA solution which would be the best way to
go.


Biff



"jacob" wrote in message


oups.com...


- Hide quoted text -
- Show quoted text -

Here's the dilemma:

I have a list validation in H7. You can choose from 6 different text
values.
Below, in H8, I would like to respond "true" (or yes) if a certain 1
of
the 6 text values is EVER selected, and then not be changed by anyone

else.


For instance, if the "flagged" value is "blue", and a user ever
selects
blue from the drop-down list in H7, can I create an argument that
populates H8 with "True"? And keep it as true forever, even if you go

and change H7 to another value.


Hope this makes sense. Thanks sirs and madams!


Jacob



Reply


Biff Apr 19, 8:04 pm show options

Newsgroups: microsoft.public.excel.misc
From: "Biff" - Find messages by this author
Date: Tue, 19 Apr 2005 23:04:10 -0400
Local: Tues,Apr 19 2005 8:04 pm
Subject: Can you freeze a result in a cell?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Well, I see no other replies, so:


Goto ToolsOptionsCalculation tab.
Check Iteration


In two out of the way cells, say AA1 and AB1 enter these formulas:


AB1 =--(H7="blue")
AA1 =MAX(AA1,AB1)


Now, in H8 enter this formula:


=IF(AA1=1,TRUE,"")


If "blue" is ever selected from the drop down, H8 will return TRUE and
NEVER
change!


Biff



"Biff" wrote in message


...


- Hide quoted text -
- Show quoted text -

Hi!

You *COULD* do this using an intentional circular reference but you
should
wait until someone posts a VBA solution which would be the best way
to go.


Biff


"jacob" wrote in message
oups.com...
Here's the dilemma:


I have a list validation in H7. You can choose from 6 different text

values.
Below, in H8, I would like to respond "true" (or yes) if a certain 1
of
the 6 text values is EVER selected, and then not be changed by
anyone
else.


For instance, if the "flagged" value is "blue", and a user ever
selects
blue from the drop-down list in H7, can I create an argument that
populates H8 with "True"? And keep it as true forever, even if you
go
and change H7 to another value.


Hope this makes sense. Thanks sirs and madams!


Jacob



Reply


jacob Apr 19, 8:54 pm show options

Newsgroups: microsoft.public.excel.misc
From: "jacob" - Find messages by this author
Date: 19 Apr 2005 20:54:41 -0700
Local: Tues,Apr 19 2005 8:54 pm
Subject: Can you freeze a result in a cell?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

That is a neat trick! And it solves my quandry.


However, I fear (as you hinted at earlier) it may only be a temporary
fix. If I send this excel document (containing the below loop) will
other users have to check the iteration box under Options for it to
display/work correctly? If so, you're right, I will need a VBA.


Jacob



- Hide quoted text -
- Show quoted text -

Biff wrote:
Well, I see no other replies, so:

Goto ToolsOptionsCalculation tab.
Check Iteration


In two out of the way cells, say AA1 and AB1 enter these formulas:


AB1 =--(H7="blue")
AA1 =MAX(AA1,AB1)


Now, in H8 enter this formula:


=IF(AA1=1,TRUE,"")


If "blue" is ever selected from the drop down, H8 will return TRUE
and NEVER
change!


Biff


"Biff" wrote in message
...
Hi!


You *COULD* do this using an intentional circular reference but you

should
wait until someone posts a VBA solution which would be the best way

to go.


Biff


"jacob" wrote in message
oups.com...
Here's the dilemma:


I have a list validation in H7. You can choose from 6 different
text
values.
Below, in H8, I would like to respond "true" (or yes) if a certain

1 of
the 6 text values is EVER selected, and then not be changed by
anyone
else.


For instance, if the "flagged" value is "blue", and a user ever
selects
blue from the drop-down list in H7, can I create an argument that
populates H8 with "True"? And keep it as true forever, even if you

go
and change H7 to another value.


Hope this makes sense. Thanks sirs and madams!


Jacob



Reply


Biff Apr 19, 9:26 pm show options

Newsgroups: microsoft.public.excel.misc
From: "Biff" - Find messages by this author
Date: Wed, 20 Apr 2005 00:26:54 -0400
Local: Tues,Apr 19 2005 9:26 pm
Subject: Can you freeze a result in a cell?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Hi!


That setting stays with the file.


I'm surprised that nobody has chimed in with a "proper" solution. You
should
repost this in the Programming forum for the best solution.


Biff