Checkboxes on one sheet to copy specific cells to another sheet

OrrJohnPhilly

Okay, im new so just learning smarthsheet and ive been through vlookup (that didnt seem to work) and now IF (which did what i wanted at first but then ultimately was disappointing.

This is the setup:

Here's what im trying to do:

I have an overall recommendations sheet that allows a client to pick and choose the subsequent year in which a recommendation will occur using check boxes:

I want my clients to be able to create individual year-to-year recommendation cost plan sheet by selecting what they want to do each year on this primary sheet. When they click a box, i want the first three columns to populate on the relevant subsequent sheets: Year 1, Year 2, Year 3 as a summary by year of what they picked on the primary

I kind of got it to work using the following IF statements

=IF({ClientDFP Checkbox1} = 1, {ClientDFP 1,4}, " ")

=IF({ClientDFP Checkbox1} = 1, {ClientDFP 2,4}, " ")

=IF({ClientDFP Checkbox1} = 1, {ClientDFP 3,4}, " ")

note: the 1,4 is just what i used to name the range column 1 row 4 and so on:

It worked!

Until i dragfilled it....because of course its still referencing the same cells as the first line. Do i have to do this individually for every cell because that seems like...activeX checkboxes in excel level of frustrating

Any advice for getting this to work? Also open to other functions that will do this better.

Jeff Reisman

@OrrJohnPhilly Welcome to Smartsheet!

You may find that using INDEX/MATCH with your IF works better here, with a small tweak to the sheets. INDEX/MATCH is way better than VLOOKUP, since it's not dependent on column position in the sheet (you can move columns around and it will still work just fine.)

First thing would be to add an Autonumber system column to each of the four sheets. Name it RowID in each sheet. What this does is give you a common value to match between rows on the main sheet and rows in the Year sheets.

The syntax for INDEX/MATCH is: =INDEX({Range the values you want are in}, MATCH(value from this row on this sheet, {Value from range in remote sheet}, 0))

Now for the Year 1 sheet, what you'll want to do is use INDEX/MATCH to determine if the Year 1 checkbox on the main sheet is checked, and if it is, use INDEX/MATCH to bring over the values from the three columns. We'll start with the Recommendations column:

=IF(INDEX({ClientDFP Year 1 Checkbox Column}, MATCH(RowID@row, {ClientDFP RowID Column}, 0)) = 1, INDEX({Client DFP Recommendations Column}, MATCH(RowID@row, {ClientDFP RowID}, 0)), "")

For each of your range references, select the entire column from the ClientDFP sheet. Repeat for the two remaining columns; the only thing you need to change for each column is the range in the second INDEX (in BOLD above.)

You can then copy the formulas down each column and they'll work on every row.

Repeat for the Year 2 and Year 3 sheets.

(The links in my signature are for the formula help pages and formula error messages pages - very helpful resources for you to bookmark.)

OrrJohnPhilly

Thanks @Jeff Reisman! That formula looks like a work of art to me 😅

I'll play around and see if I can get it set up right. I'm still figuring out the system. I took an initial stab at step one and my autonumbered column randomy jumped row 6 for some reason so the rows weren't lining up across sheets. I needed to step away from the sheets for a bit and then try again. Will update here as it comes together!

Thanks again!

OrrJohnPhilly

Okay, still working on the formula above - but in the meantime, I think i found another option...

I just linked the first three columns of ClientDFP to Year 1. This will let my clients edit the Recommendations column and the cost column as they secure actual vendors and estimates and it updates across the sheets. That made the first three columns of ClientDFP and Year 1 identical. I added a checkbox column to Year 1, and linked that checkbox to the checkbox on the ClientDFP so that the primary sheet checkbox controlled the Year 1 checkbox. Then i applied a conditional format to the Year 1 check box to change of the row to bold, black. then i turned most of the text on Year 1 to white, and hid the checkbox column.

...and it seems to be doing exactly what i want it to do....

Now i just need a formula for autosumming the cost column on the Year sheets, but i only want to sum the numbers in black - because all the other number are there - they're just in white 😂

Jeff Reisman

https://community.smartsheet.com/discussion/comment/357518#Comment_357518

Sorry for the delay in responding. I was stuck in a 3 day meeting last week and slammed so far this week.

To only add the bold values, you can use a SUMIFS formula with the same criteria that your conditional formatting is using:

=SUMIFS([Estimated Cost]:[Estimated Cost], [Year 1]:[Year 1], 1)

In English: Add the values in the Estimated Cost column where the Year 1 checkbox is checked.

OrrJohnPhilly

Hi Jeff! Thank you so much for your help, the sumifs based on check box value is exactly what i needed. It worked perfectly! I also added a filter function on the Year 1, 2 & 3 pages to only display rows that were checked and i didn't event have to deal with the changing text color.

Thanks again!

John

Will Collinsworth

Hey Jeff-

I'm trying to something similar here- but a bit different...I'm trying to match the above but not having much luck. What I want to do is have the cell "Tag" from our Equipment Log, and the Cell "Description" populate onto our "ID Schedule" if the "Include in ID" checkbox is checked. I then want it to auto-filter so just those checked items are shown on the sheet.

This is what I have so far:

Jeff Reisman

https://community.smartsheet.com/discussion/comment/405975#Comment_405975

It looks like so far you have the logical expression portion of the IF formula. Now you need to add the value if true, and optionally the value if false. So after the = 1 in your formula, add a comma followed by the INDEX/MATCH looking for the "Tag" value from the other sheet. Then just close out the formula with close parentheses, making sure the last one matches the color of the very first open parentheses. (Leaving off a "value if false" results in the cell just being left empty in a false condition.) Do the same for your "Description" value.