Using a range of checkboxes for an IF statement

crgcrg_zeb · 04/25/22

Hi, I have a column of checkboxes, and next to them are cells with just regular text. I want the text to be referenced in another sheet only if the checkbox is checked:

I can get it to work for just one value fine with =IF({checkbox reference}, {personal goal reference}), however this is just one pair of cells and I would like to do the entire column. If my {checkbox reference} is a range of cells(the whole column) I get a #INVALID DATA TYPE error (I guess the IF() function cannot take a range for its arguments).

Is there a way around this? I have also tried using INDEX inside the IF function, but it throws an #INVALID COLUMN VALUE error when given a checkbox as its first argument...

Thanks in advance

Kevin Smith · 04/25/22

Can you tell more about the sheet where you want the text to be referenced? First thing I thought of was this looks more like a report would solve the problem and you can use the check box as a filter, but if the sheet you want to show the text on is something else entirely, then my next thought is that the sheet might need a common value to use a look up on.

Let me know if those comments spawn any ideas or let me know more about the second sheet, I am sure there is some way to solve what you are wanting.

crgcrg_zeb · 04/25/22

Hey Kevin,

The sheet I want to reference the values on is just another regular sheet, unfortunately using a report is not an option for me at this time. I just want to populate the corresponding rows in the second sheet, with the text from the first sheet but only if the checkbox is checked.

I had a small idea of adding a row to my first sheet next to the checkboxes, to see if I could get it working with a range of characters:

I'm pretty new and not great with smartsheet however, so I am having trouble getting this to work in conjunction with the INDEX function. What can I put in the row index argument for INDEX that will change it based on the row it is in? Thanks

Paul H · 04/25/22

You mentioned "corresponding rows", how are these row identified? If you have a unique identifier you can use a nested Index/match formula.

crgcrg_zeb · 04/26/22

https://community.smartsheet.com/discussion/comment/327303#Comment_327303

Hi Paul, not sure what you mean by how they are identified. But, they essentially line up between the two sheets. So if the text from the first sheet is on row X, it will get rolled up to the second sheet on row X. That may make it easier?

Kevin Smith · 04/26/22

Hi @crgcrg_zeb

I think I get it and I certainly can relate to being new to Smartsheet. Paul's suggestion will work but if you are not comfortable with Index/match, you can try this....

Do a cell link for the whole column. (probably better ways but this will work. If your add and remove rows, then just use this to get by and do some research into Index/match)

Do this cell link from the sheet you want the comments on in a new col. of checkbox type , you can even call it the same name or something that tells you it is from the first sheet. (if you don't use a checkbox type, it will still work you will just get blank or the word 'true' in the row)

Once you have that, you will see the actual check boxes checked and they will match up. (Caution, if they do not match up, then don't do this, you will have to do the index/match.

Once you have that col, you can do just an index something like this:

=IF([text from other grid]@row = "true", INDEX({XL Grid Range 4}, RowNum@row), "") or if you are using The XL Grid Range 4 is just the sheet you have the text on and the column of with the text highlighted.

In this image, the Column 5 (personal goals) would be the col. you would highlight (select) when setting up the "Reference Another Sheet" when setting up the INDEX part of the If statement. CB1 is the col. with the check boxes that you would have use to link to.(Rollup)

The resulting sheet would look lime this for the Test Col and the Text from other grid. Test col, is the one with the IF/Index formula, and Text from other grid is a col. with the cell links to the Rollup col.

The little blue triangles show the cells are being referenced.

Please remember this is just a stop gap, something that might be quicker and easier than index/match but use only if you need a quick solution. Long term, Index/match is the right way to go to keep from having problems or issues later on.

Please let me know what questions you have.

Using a range of checkboxes for an IF statement

Answers

Help Article Resources

Categories