Can you freeze a result in a cell?

[jacob]

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

[Biff]

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" <jfarino@mindspring.com> wrote in message
news:1113935648.228530.178400@z14g2000cwz.googlegroups.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
>

[Biff]

Well, I see no other replies, so:

Goto Tools>Options>Calculation 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" <biffinpitt@comcast.net> wrote in message
news:OBlhULRRFHA.2604@TK2MSFTNGP10.phx.gbl...
> 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" <jfarino@mindspring.com> wrote in message
> news:1113935648.228530.178400@z14g2000cwz.googlegroups.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
>>
>
>

[jacob]

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


Biff wrote:
> Well, I see no other replies, so:
>
> Goto Tools>Options>Calculation 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" <biffinpitt@comcast.net> wrote in message
> news:OBlhULRRFHA.2604@TK2MSFTNGP10.phx.gbl...
> > 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" <jfarino@mindspring.com> wrote in message
> > news:1113935648.228530.178400@z14g2000cwz.googlegroups.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
> >>
> >
> >

[Biff]

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

"jacob" <jfarino@mindspring.com> wrote in message
news:1113969281.608719.198200@z14g2000cwz.googlegroups.com...
> 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
>
>
> Biff wrote:
>> Well, I see no other replies, so:
>>
>> Goto Tools>Options>Calculation 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" <biffinpitt@comcast.net> wrote in message
>> news:OBlhULRRFHA.2604@TK2MSFTNGP10.phx.gbl...
>> > 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" <jfarino@mindspring.com> wrote in message
>> > news:1113935648.228530.178400@z14g2000cwz.googlegroups.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
>> >>
>> >
>> >
>