Using IF to return a value from another cell
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))))
Answers
-
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))))
-
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))))
-
Try somethign like this...
=IF(AND(Metric4 = "Refurbishment", Metric10 = "Germany"), [Germany Refurbishment]@row, IF(Metric10 = "Germany", Germany@row, IF(Metric10 = "Netherlands", Netherlands@row)))
-
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))))); "")
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!