Trouble Referencing Same-Sheet Cell in a Logical Expression (IF or OR)
Hi all,
I know this has been asked and answered a number of times, but none of the solutions previously given have worked for me. Maybe my formula is special...I dunno.
I have an expense planning sheet, and my client wants to be able to include options (option 1, Option 2, etc.) for some line items, and a way to roll-up costs by each option, omitting others.
Since filters don't remove a hidden row from the roll-up sums, I had to go about it a different way; I'm using a cell in row 1 (on the same sheet) as a picker that will inform the Projected Cost formula which options to omit from calculation.
I cannot get it to work, and I've tried about 10 variations at this point.
In the formula that I've landed on;
=IF(Options@row = "", IF(COUNT(CHILDREN([Item or Service]@row)) = 0, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), SUM(CHILDREN())), IF(Options@row = [Account Code]1, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), 0))
The trouble lies in the bolded piece of the formula. No matter how I try to get around it, it just throws a syntax error:
However, if I change [Account Code]1 to just a 1 (or 2 or 3), the formula works perfectly:
Could it be that the ColumnA@row format doesn't play nice with the in-sheet reference, but ColumnA1 does?
Does anyone see a way to make this work?
Best Answer
-
Coming back to report that I have found a workaround.
I moved the "options-to-factor" selector from [Account Code]1 and placed it as a field in the Sheet Summary, then modified the formula accordingly.
=IF(Options@row = "", IF(COUNT(CHILDREN([Item or Service]@row)) = 0, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), SUM(CHILDREN())), IF(Options@row = [Option Selector]#, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), 0))
This is working well, but I'm still curious about the earlier predicament. I can imagine some instances where I might run into the same issue.
-Kevin
Answers
-
Coming back to report that I have found a workaround.
I moved the "options-to-factor" selector from [Account Code]1 and placed it as a field in the Sheet Summary, then modified the formula accordingly.
=IF(Options@row = "", IF(COUNT(CHILDREN([Item or Service]@row)) = 0, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), SUM(CHILDREN())), IF(Options@row = [Option Selector]#, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), 0))
This is working well, but I'm still curious about the earlier predicament. I can imagine some instances where I might run into the same issue.
-Kevin
-
Hey @Kevin M.
Glad you worked it out! Thank you for posting your solution!
Join us at Smartsheet ENGAGE 2024🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!