How to set a column values base on the selection of another choice column? In sharepoint lists

Question

Hey,

I'd like to assign value based on choice column "CARS" to another value column "Price" so if I choose "Porsche" from my list, I want to assign a price in the column "Price".

I could not do it with calculated column, since choice column is not "allowed"

thanks.

Answer 1

Hi @Jack ,

Do you mean that the value of the "Price" column needs to be automatically filled according to the value of the Choice column "CARS"? If so, you can use a calculated column. This is my test:

1. Create a calculated column named Price and fill it with the following formula:

=IF(CARS="Choice 1","200",IF(CARS="Choice 2","400",IF(CARS="Choice 3","600","")))

You can change the value in the formula according to your actual needs.

2. When different values ​​are selected in the CARS column, the Price column will automatically fill in the corresponding value.

Good day!

---

If the answer is helpful, please click "Accept as Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Great, thank you it's working. I've created new option column, cuz my original one wasn't showing in the field "insert column".

Is there a way how to choose two options, for example choice 1 and choice 2 in one column and sum the price ?

Answer 3

Hi @Jack ,

The calculated column is used to reference the value in the column. There are too many values ​​in the multiple-select column, and the reference will be complicated, so the multiple-select column does not currently support calculated columns.

There is a workaround for your reference: put the two values ​​of the multiple-select column into two single-select columns.

Formula:

=CARS+Choice

The result:

Hope this can help.

---

If the answer is helpful, please click "Accept as Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi there, how many options can I use in the formula? I used 24 with IF, but it's not working....1 tried remove some...I put there maybe 18 and it was working...

this is the formula I used:

=IF([Procedúra 1]="xxxx","69€",

IF([Procedúra 1]="xx","79€",

IF([Procedúra 1]="xx","99€",

IF([Procedúra 1]="xx","89€",

IF([Procedúra 1]="xx","75€",

IF([Procedúra 1]="xx","139€",

IF([Procedúra 1]="xx","139€",

IF([Procedúra 1]="xx","120€",

IF([Procedúra 1]="xx","80€",

IF([Procedúra 1]="xxm","70€",

IF([Procedúra 1]="xxn","79€",

IF([Procedúra 1]="xx","119€",

IF([Procedúra 1]="xx","79€",

IF([Procedúra 1]="xx","190€",

IF([Procedúra 1]="xx","390€",

IF([Procedúra 1]="xx","190€",

IF([Procedúra 1]="xx","190€",

IF([Procedúra 1]="xx","65€",

IF([Procedúra 1]="xx","45€",

IF([Procedúra 1]="xx","55€",

IF([Procedúra 1]="xx","69€",

IF([Procedúra 1]="xx","69€",

IF([Procedúra 1]="xx","55€",

IF([Procedúra 1]="xx ","69€", ""))))))))))))))))))))))))

Answer 5

Hi @Jack ,

Yes, there is a limit to the number of nested If statements.

SharePoint 2007 and 2010 only allow 7. SharePoint 2013 and later (including Online) allows 19. Larger values ​​are possible by using batches of 7 or 19 or less and concatenating or adding the results.

You can divide the 24 if statements into two groups and connect them with the & operator to achieve your needs.

=IF([Procedúra 1]="xxxx","69€",
IF([Procedúra 1]="xx","79€",
IF([Procedúra 1]="xx","99€",
IF([Procedúra 1]="xx","89€",
IF([Procedúra 1]="xx","75€",
IF([Procedúra 1]="xx","139€",
IF([Procedúra 1]="xx","139€",
IF([Procedúra 1]="xx","120€",
IF([Procedúra 1]="xx","80€",
IF([Procedúra 1]="xxm","70€",
IF([Procedúra 1]="xxn","79€",
IF([Procedúra 1]="xx","119€",""))))))))))))&
IF([Procedúra 1]="xx","79€",
IF([Procedúra 1]="xx","190€",
IF([Procedúra 1]="xx","390€",
IF([Procedúra 1]="xx","190€",
IF([Procedúra 1]="xx","190€",
IF([Procedúra 1]="xx","65€",
IF([Procedúra 1]="xx","45€",
IF([Procedúra 1]="xx","55€",
IF([Procedúra 1]="xx","69€",
IF([Procedúra 1]="xx","69€",
IF([Procedúra 1]="xx","55€",
IF([Procedúra 1]="xx ","69€", ""))))))))))))

There is a similar post for your reference:

https://answers.microsoft.com/en-us/msoffice/forum/all/nested-if-in-sharepoint-list/c158697c-9f45-4ce2-9fe0-6b18d9e2d40f

---

If the answer is helpful, please click "Accept as Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.