Automatically assigning a value based on another column value

[NicosAcuna]

Hello All,

I was wondering if there is a process in Sharepoint maybe using power automate that will help me fill a column automatically depending on another column value. 

As an example I have 26 Departments listed as possible choices in column A

I want Column B to auto generate a DepartmentID based on the department selected in column A

So every time HR (one of the 26 departments)  is selected in column A it will generate a "1" in column B.

Every TIme Operations is selected in column A it will auto populate a "2" in column B.

And so on and so fourth, my apologies if there is a simple solution, I'm quite new to using SharePoint and power apps.

[#2]

@NicosAcuna well you could do it with a Switch control in Power Automate as you would be just inside the 27 case limit, but it's more work than having a calculated column using multiple If expressions. So your department column would be a choice column then you'd add the following formula to your DeptID calculated column:

=If(Department="HR",1,If(Department="Operations",2,If(Department="Warehouse",3,"")))

etc etc.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#3]

@RobElliott Thank you for this solution! I was able to get this to work finally! I ran into another issue however. Lets say the choice column was multiselect, meaning you could select multiple options. How would I go about adding the points up on the second column? 

Below is my example and error I'm getting: 

(I have three selectable options and gave them all 2 points each for a total of 6 points. Lets say I were to select "Multiple Users" and "20 Users," that should equal up to 4 points. If I selected all three options from the drop down menu, it would be 6 points. So I have two columns related to this. First column is "Collaboration" and second column is "CollaborationCalculated" which is a Calculated Field. When I add this formula below in the "CollaborationCalculated" column it works until I make the "Collaboration" column to Allow multiple selections. I get the below error which says: "unsupportedFieldTypeError." Is there an IF statement with addition involved to make this work?) 

=IF([Collaboration]="Multiple Users",2,

IF([Collaboration]="20 Users",2,

IF([Collaboration]="10 Users",2,"")))

[#4]

Hi, @RobElliott 

I have the same problem in this post, but if I have any change in the number of departments or even names I would have to return to this formula and adjust, am I right?

Is there any other solution that would handle variables for Departments?

[#5]

@Cuba_RJ yes you would have to update it manually, it wouldn't update dynamically.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

[#6]

This doesn't work for me I get an error:
Sorry, something went wrong
One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.

One column is choice, the other column is my calculated column which puts the text I want.

[#7]

@RobElliott I have a list grouped by State and I want to add a Solution ID column that I want to populate a unique ID based on the state selected. This does not seem to be working for me. How can I accomplish? All states have a unique ID # that is 15 numbers long. =IF(State=“AL”,201028145313666,IF(State=“AK”,201028145313777,”)

[#8]

@HugoVital I assume by "Solution ID column" you mean a calculated column. You need to use a formula like this:

=IF(State="AL","201028145313666",IF(State="AK","201028145313777",IF(State="AZ","196745986234901","0")))

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

[#9]

@Rob_Elliott I get a message stating “The validation formula can refer to this column and not others.” I am using a Choice column by the way.

[#10]

@HugoVital 

I think you are trying to add the formula in Column Validation section of existing column - which is causing this error.

As per the suggestion by @Rob_Elliott, you have to create a new calculated column with the formula. Check: Create a column in a list or library 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.