Trouble Referencing Same-Sheet Cell in a Logical Expression (IF or OR)

Kevin M.

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?

Kevin M.

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

Kevin M.

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

Marcela

Hey @Kevin M.

Glad you worked it out! Thank you for posting your solution!