Nest an IF Formula
With the help of the community, I have two formulas that I would like to combine into one if possible.
Formula 1
=IF(Ancestors@row = 0, COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), "")
Formula 2
=IF(LEN(RowID@row) = 1, "000" + RowID@row, IF(LEN(RowID@row) = 2, "00" + RowID@row, IF(LEN(RowID@row) = 3, "0" + RowID@row, RowID@row)))
My failed try
This throws an #INCORRECT ARGUMENT SET error
=IF(ANCESTORS@row = 0, COUNTIF(ANCESTORS$1:ANCESTORS@row, ANCESTORS@row = 0, "", (IF(LEN(RowID@row) = 1, "000" + RowID@row, IF(LEN(RowID@row) = 2, "00" + RowID@row, IF(LEN(RowID@row) = 3, "0" + RowID@row, RowID@row))))))
Best Answer
-
Taking another gander, this may be what you are looking for...
=IF(Ancestors@row = 0, IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 1, "000" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 2, "00" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 3, "0" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0))), "")
Answers
-
How exactly are you trying to combine them? What is the desired end result?
-
Hi Stavros,
Have you tried to change the order of the formulas IF parts?
I hope that helps!
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Taking another gander, this may be what you are looking for...
=IF(Ancestors@row = 0, IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 1, "000" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 2, "00" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), IF(LEN(COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0)) = 3, "0" + COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0), COUNTIF(Ancestors$1:Ancestors@row, Ancestors@row = 0))), "")
-
Thanks all... With a little bit of manipulation, I got this to work as expected
-
Excellent!
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Excellent! Happy to help! 👍️
-
Excellent! Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!