Forum Discussion

RobElliott's avatar
RobElliott
Silver Contributor
Mar 03, 2021

Re: Automatically assigning a value based on another column value

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

8 Replies

  • HugoVital's avatar
    HugoVital
    Copper Contributor
    Feb 28, 2024

    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,”)

    • Rob_Elliott's avatar
      Rob_Elliott
      Silver Contributor
      Feb 28, 2024

      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)

      • HugoVital's avatar
        HugoVital
        Copper Contributor
        Feb 28, 2024

        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.

  • Awesome390's avatar
    Awesome390
    Copper Contributor
    Dec 19, 2023
    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.
  • Cuba_RJ's avatar
    Cuba_RJ
    Copper Contributor
    Oct 11, 2023

    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?

    • Rob_Elliott's avatar
      Rob_Elliott
      Silver Contributor
      Oct 11, 2023

      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)