I am looking to assign a value to a text string and then insert the
corresponding value into a cell

As follows:

A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" then I1 =
"20"
If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" then I2 =
"20"

then this to loop until A100

any help will be gratefully received

Give this a try...

=10+10*(A1="FOR CHANGE")

and copy it down as needed.

--
Rick (MVP - Excel)


"Robincambs" <(E-Mail Removed)> wrote in message
news:cf08ca94-9d6c-4e28-b423-(E-Mail Removed)...
>I am looking to assign a value to a text string and then insert the
> corresponding value into a cell
>
> As follows:
>
> A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
> If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" then I1 =
> "20"
> If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" then I2 =
> "20"
>
> then this to loop until A100
>
> any help will be gratefully received

Enter
=IF(A1="NO CHANGE",10,20)
and drag it down to A100.

Regards,
Stefi

„Robincambsâ€� ezt Ã*rta:

> I am looking to assign a value to a text string and then insert the
> corresponding value into a cell
>
> As follows:
>
> A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
> If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" then I1 =
> "20"
> If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" then I2 =
> "20"
>
> then this to loop until A100
>
> any help will be gratefully received
>

On 22 Sep, 14:17, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Give this a try...
>
> =10+10*(A1="FOR CHANGE")
>
> and copy it down as needed.
>
> --
> Rick (MVP - Excel)
>
> "Robincambs" <marvellous...@gmail.com> wrote in message
>
> news:cf08ca94-9d6c-4e28-b423-(E-Mail Removed)...
>
>
>
> >I am looking to assign a value to a text string and then insert the
> > corresponding value into a cell
>
> > As follows:
>
> > A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
> > If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" then I1 =
> > "20"
> > If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" then I2 =
> > "20"
>
> > then this to loop until A100
>
> > any help will be gratefully received- Hide quoted text -
>
> - Show quoted text -

Thanks for the suggestion, it doesn't do what i was hoping. There are
2 possible entries of text "NO CHANGE" or "FOR CHANGE" the suggestion
you made only provives a result of 10

Thanks
Rob

What I posted will return 20 if A1 contains "FOR CHANGE" and will return 10
otherwise. Perhaps you mean it returns 10 if A1 is blank? Try this then...

=IF(A1="","",10+10*(A1="FOR CHANGE"))

--
Rick (MVP - Excel)


"Robincambs" <(E-Mail Removed)> wrote in message
news:f6507ff7-af8b-4e85-8c57-(E-Mail Removed)...
> On 22 Sep, 14:17, "Rick Rothstein"
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>> Give this a try...
>>
>> =10+10*(A1="FOR CHANGE")
>>
>> and copy it down as needed.
>>
>> --
>> Rick (MVP - Excel)
>>
>> "Robincambs" <marvellous...@gmail.com> wrote in message
>>
>> news:cf08ca94-9d6c-4e28-b423-(E-Mail Removed)...
>>
>>
>>
>> >I am looking to assign a value to a text string and then insert the
>> > corresponding value into a cell
>>
>> > As follows:
>>
>> > A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
>> > If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" then I1 =
>> > "20"
>> > If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" then I2 =
>> > "20"
>>
>> > then this to loop until A100
>>
>> > any help will be gratefully received- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks for the suggestion, it doesn't do what i was hoping. There are
> 2 possible entries of text "NO CHANGE" or "FOR CHANGE" the suggestion
> you made only provives a result of 10
>
> Thanks
> Rob

On 22 Sep, 14:25, Stefi <St...@discussions.microsoft.com> wrote:
> Enter
> =IF(A1="NO CHANGE",10,20)
> and drag it down to A100.
>
> Regards,
> Stefi
>
> „Robincambs” ezt írta:
>
>
>
> > I am looking to assign a value to a text string and then insert the
> > corresponding value into a cell
>
> > As follows:
>
> > A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
> > If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" thenI1 =
> > "20"
> > If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" thenI2 =
> > "20"
>
> > then this to loop until A100
>
> > any help will be gratefully received- Hide quoted text -
>
> - Show quoted text -

Unfortunatly neither of those work - perhaps its due to me using
conditional formatting or dropdown lists?

On 22 Sep, 14:47, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> What I posted will return 20 if A1 contains "FOR CHANGE" and will return 10
> otherwise. Perhaps you mean it returns 10 if A1 is blank? Try this then...
>
> =IF(A1="","",10+10*(A1="FOR CHANGE"))
>
> --
> Rick (MVP - Excel)
>
> "Robincambs" <marvellous...@gmail.com> wrote in message
>
> news:f6507ff7-af8b-4e85-8c57-(E-Mail Removed)...
>
>
>
> > On 22 Sep, 14:17, "Rick Rothstein"
> > <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> >> Give this a try...
>
> >> =10+10*(A1="FOR CHANGE")
>
> >> and copy it down as needed.
>
> >> --
> >> Rick (MVP - Excel)
>
> >> "Robincambs" <marvellous...@gmail.com> wrote in message
>
> >>news:cf08ca94-9d6c-4e28-b423-(E-Mail Removed)...
>
> >> >I am looking to assign a value to a text string and then insert the
> >> > corresponding value into a cell
>
> >> > As follows:
>
> >> > A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
> >> > If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" then I1 =
> >> > "20"
> >> > If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" then I2 =
> >> > "20"
>
> >> > then this to loop until A100
>
> >> > any help will be gratefully received- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks for the suggestion, it doesn't do what i was hoping. There are
> > 2 possible entries of text "NO CHANGE" or "FOR CHANGE" the suggestion
> > you made only provives a result of 10
>
> > Thanks
> > Rob- Hide quoted text -
>
> - Show quoted text -

Yes - that works great, how could i address 3 different conditions
such as NO CHANGE" or "FOR CHANGE" or "PENDING CHANGE"

> Unfortunatly neither of those work - perhaps its due to me using
> conditional formatting or dropdown lists?

I don't think so, something else must be the problem, either Rick's solution
or mine should work, maybe your data contain some factor (case, redundant
spaces, etc.) Please post sample data!

Stefi

It is usually easier if you follow the same posting method as the person who
responded to you... in my case, posting the responses at the top of the
message (that way the text is not bouncing back and forth from top to
bottom).

As for your problem... conditional formatting or dropdown lists should not
affect the formulas that have been posted from working. I am going to guess
that you have more in your cells than NO CHANGE or FOR CHANGE... perhaps
some extra spaces at the front or back?

--
Rick (MVP - Excel)


> Unfortunatly neither of those work - perhaps its due to me using
> conditional formatting or dropdown lists?

Sorry about the user group etiquette, something i'm not farmiliar
with.

I have found your suggestion works fine - it was my formatting that
was at fault.

how could i address 3 different conditions and assign 3 values
such as NO CHANGE" = 10 or "FOR CHANGE" = 20 or "PENDING CHANGE" = 30

Kind regards
Rob

On 22 Sep, 15:05, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> It is usually easier if you follow the same posting method as the person who
> responded to you... in my case, posting the responses at the top of the
> message (that way the text is not bouncing back and forth from top to
> bottom).
>
> As for your problem... conditional formatting or dropdown lists should not
> affect the formulas that have been posted from working. I am going to guess
> that you have more in your cells than NO CHANGE or FOR CHANGE... perhaps
> some extra spaces at the front or back?
>
> --
> Rick (MVP - Excel)
>
>
>
> > Unfortunatly neither of those work - perhaps its due to me using
> > conditional formatting or dropdown lists?- Hide quoted text -
>
> - Show quoted text -