Trouble Referencing SameSheet 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 rollup costs by each option, omitting others.
Since filters don't remove a hidden row from the rollup 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 insheet 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 "optionstofactor" 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 "optionstofactor" 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!
Help Article Resources
Categories
Check out the Formula Handbook template!