Using IF to return a value from another cell

Options

Hi All,

Currently we have the below system in place to return the value of a specific cell out of a specific column. So for example, If the country is Germany it pulls the information out of the Germany Column. This works great!

But now comes my problem. We have added a Germany Refurbishment Column. So now it should work like this: If country is Germany and Project Type is Refurbishment then it should pull the info out of the Germany Refurbishment Column(but only for Germany). But no mather what I tried i can't seem to get the formula to work.

Do you have any suggestions?

Thanks in advance!


Below is the formula in the screenshot:

=IF($Metric$10 = "Netherlands"; Netherlands@row; IF($Metric$10 = "France"; France@row; IF($Metric$10 = "Germany"; Germany@row; IF($Metric$10 = "Belgium"; Belgium@row))))

Tags:

Answers

  • DesireeJones
    DesireeJones ✭✭✭✭✭
    Options

    Hello @Jeffrey Wagemans ,

    Where is your project type "Refurbishment" supposed to be?

    Are you wanting to include any of the dates columns?

    This formula with an OR statement included should lay the foundation of the formula you have so far:

    =IF(OR(Metric@row = "Netherlands"), Netherlands@row, IF(OR(Metric@row = "France"), France@row, IF(OR(Metric@row = "Germany"), Germany@row, IF(OR(Metric@row = "Belgium"), Belgium@row))))

  • Jeffrey Wagemans
    Options

    HI @DesireeJones ,

    Thanks for your answer!

    Refurbishment will be automatically filled into the metric column whenever we create a refurbishment project.

    I have tried your foundation for the formula but I can't seem to get it to work the right way.

    The way I think it should work is like this:

    If Country in column ''metric'' row 10 is Netherlands, then fill in the info from column Netherlands. If country is Germany in column ''metric'' row 10 is Germany then fill in the info from column Germany. etc. etc.

    But if Country in column ''metric''- row 10 is Germany and if Project Type in column "Metric"- row 4 is Refurbishment then it should pull the information out of the Germany Refurbishment column.

    I tried the formula below but I think I'm doing something wrong because I'm getting the unparseable error.

    =IF(OR($Metric$10 = "Netherlands"); Netherlands@row; IF(OR($Metric$10 = "France"); France@row; IF(OR($Metric$10 = "Germany"; $Metric$4 = "Refurbishment"); [Germany Refurbishment]@row; Germany@row; ; IF(OR($Metric$10 = "Belgium"); Belgium@row))))


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try somethign like this...


    =IF(AND(Metric4 = "Refurbishment", Metric10 = "Germany"), [Germany Refurbishment]@row, IF(Metric10 = "Germany", Germany@row, IF(Metric10 = "Netherlands", Netherlands@row)))

  • Jeffrey Wagemans
    Options

    Hi @Paul Newcome,

    Thanks! This worked!!

    Is it also possible to do the same thing but then with the dates column? We currently use the formula below for that column but if I try to add the AND Function i get the incorrect argument set message.

    =IFERROR(IF($Metric$10 = "Netherlands"; YEAR([Netherlands Dates]@row) + "-" + MONTH([Netherlands Dates]@row) + "-" + DAY([Netherlands Dates]@row); IF($Metric$10 = "France"; YEAR([France Dates]@row) + "-" + MONTH([France Dates]@row) + "-" + DAY([France Dates]@row); IF($Metric$10 = "Germany"; YEAR([Germany Dates]@row) + "-" + MONTH([Germany Dates]@row) + "-" + DAY([Germany Dates]@row); IF($Metric$10 = "Belgium"; YEAR([Belgium Dates]@row) + "-" + MONTH([Belgium Dates]@row) + "-" + DAY([Belgium Dates]@row))))); "")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It would look something like this:

    =IFERROR(IF(AND(Metric4 = "Refurbishment"; Metric10 = "Germany"); output_for_true; IF($Metric$10 = "Netherlands"; YEAR([Netherlands Dates]@row) + "-" + MONTH([Netherlands Dates]@row) + "-" + DAY([Netherlands Dates]@row); IF($Metric$10 = "France"; YEAR([France Dates]@row) + "-" + MONTH([France Dates]@row) + "-" + DAY([France Dates]@row); IF($Metric$10 = "Germany"; YEAR([Germany Dates]@row) + "-" + MONTH([Germany Dates]@row) + "-" + DAY([Germany Dates]@row); IF($Metric$10 = "Belgium"; YEAR([Belgium Dates]@row) + "-" + MONTH([Belgium Dates]@row) + "-" + DAY([Belgium Dates]@row)))))); "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!