Formulas with cells containing cross-sheet references

Steve Rogers

I have a sheet where I'm collecting survey results (using the COUNTIF function) from another sheet. It's a simple survey, all questions are true/false, and I am trying to create a formula that will list which result is dominant for each question. In other words, if I get more "True" responses than "False", I want the formula to return Introvert as the dominant answer (if "False" responses are greater than "True" the formula would return Extrovert as the answer, and if they're the same it's a Tie). When I try to run any kind of simple formula off the two columns running the COUNTIF formulas (pulling from the destination sheet), I always get an #UNPARSABLE error message. Trying a different approach I get an #INCORRECT ARGUMENT SET error, and that may be a formula error.

Below is an image showing a formula I'm currently trying with no success:

Is it not possible to create a formula using cells with data derived from an cross-sheet reference? Is there an error in my formula? As I stated above, even a simple formula like =True@row - False@row returns an #UNPARSABLE error. If I can't do the formula as written above because of a system limitation, I'd love any ideas for a workaround.

Any help you can offer would be appreciated.

Chris Mondeau

Hi @SteveRogers ,

I believe the issue here is with reserved strings from Smartsheet's formulas. Since True and False are used for other boolean logic, this can create errors in formulas you make (hence why there's no colors in your equation).

I used your same formulas, but renamed the "True" and "False" Columns to INT and EXT, and everything worked fine.

Maybe this will help!

Paul Newcome

@cmondo is correct. It is the column names of True and False. To get around this while using those column names, wrap them in square brackets in your formula.

[True]@row

[False]@row

The square brackets indicate a column name, so the formula is not looking for the boolean value anymore. This will allow you to keep your column names as is and get your formulas working.

Steve Rogers

@cmondo @Paul Newcome

First off, thanks for the tips! It definitely works to add the brackets, but I ran into a snag:

It works while I fix the formula (I get colors), but as soon as I hit enter the brackets on True and False disappear. As soon as I refresh the page I go from getting the correct answer to getting #UNPARSABLE again. See the image below (top is while I edit, bottom is after I hit Enter):

Maybe I need to quite using True and False and instead try your INT and EXT?

Paul Newcome

@SteveRogers Sorry about that. I hadn't tested refreshing the sheet, but I see now that I too get the same results. Unfortunately it looks like you are going to need to rename your columns.

I do suggest that you Submit a Product Enhancement Request regarding using "True" and "False" as column names when you have a moment.