Using IF to return a value from another cell

Jeffrey Wagemans

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

DesireeJones

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

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

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

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

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)))))); "")