|
Assigning value to text
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 |
Assigning value to text
Give this a try...
=10+10*(A1="FOR CHANGE") and copy it down as needed. -- Rick (MVP - Excel) "Robincambs" wrote in message ... 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 |
Assigning value to text
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 |
Assigning value to text
On 22 Sep, 14:17, "Rick Rothstein"
wrote: Give this a try... =10+10*(A1="FOR CHANGE") and copy it down as needed. -- Rick (MVP - Excel) "Robincambs" wrote in message ... 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 |
Assigning value to text
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" wrote in message ... On 22 Sep, 14:17, "Rick Rothstein" wrote: Give this a try... =10+10*(A1="FOR CHANGE") and copy it down as needed. -- Rick (MVP - Excel) "Robincambs" wrote in message ... 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 |
Assigning value to text
On 22 Sep, 14:25, Stefi 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" 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 - Unfortunatly neither of those work - perhaps its due to me using conditional formatting or dropdown lists? |
Assigning value to text
On 22 Sep, 14:47, "Rick Rothstein"
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" wrote in message ... On 22 Sep, 14:17, "Rick Rothstein" wrote: Give this a try... =10+10*(A1="FOR CHANGE") and copy it down as needed. -- Rick (MVP - Excel) "Robincambs" wrote in message ... 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" |
Assigning value to text
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 |
Assigning value to text
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? |
Assigning value to text
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" 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 - |
Assigning value to text
You should always ask for what you want initially and not simplify your
requests for us... the nature of formula (and even macro) solutions are that they tend to be good only for the conditions they were designed to handle and no more. For your current question, which did not tell us what you wanted for the PENDING CHANGE condition (so I assumed 30 in my sample formula), try this... =IF(A1="","",IF(A1="NO CHANGE",10,IF(A1="FOR CHANGE",20,30))) -- Rick (MVP - Excel) Yes - that works great, how could i address 3 different conditions such as NO CHANGE" or "FOR CHANGE" or "PENDING CHANGE" |
Assigning value to text
Super - thanks Rick, i have learnt a few lessons today...!
On 22 Sep, 15:29, "Rick Rothstein" wrote: You should always ask for what you want initially and not simplify your requests for us... the nature of formula (and even macro) solutions are that they tend to be good only for the conditions they were designed to handle and no more. For your current question, which did not tell us what you wanted for the PENDING CHANGE condition (so I assumed 30 in my sample formula), try this... =IF(A1="","",IF(A1="NO CHANGE",10,IF(A1="FOR CHANGE",20,30))) -- Rick (MVP - Excel) Yes - that works great, how could i address 3 different conditions such as NO CHANGE" or "FOR CHANGE" or "PENDING CHANGE"- Hide quoted text - - Show quoted text - |
Assigning value to text
=IF(A1="NO CHANGE",10,IF(A1="FOR CHANGE",20,30))
Regards, Stefi €˛Robincambs€¯ ezt Ć*rta: 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" 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 - |
| All times are GMT +1. The time now is 10:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com