Formula based off Checkbox selected

KempenUSA

I am looking for the formula that would return values from another sheet based on the checkbox being checked.

I have a data sheet, which contains predefined hrs and rates for a specific deliverable. In another sheet I want to these values pulled into once the checkbox is checked.

please see 2 pictures. E.g. if I check the checkbox in row 5 I want the values in column deliverables hrs to reflect 16 and charge rate to reflect 2251.

Sandy Drew

You can create column formulas in your Document Deliverables sheet for the Hrs and Rate columns as below.

=IF(Deliverables@row = 1, INDEX({Total Hrs}, MATCH([Document Title]@row, {Doc/Dwg Title}, 0)))

=IF(Deliverables@row = 1, INDEX({Total Chrg}, MATCH([Document Title]@row, {Doc/Dwg Title}, 0)))

Kerry St. Thomas

If you check the little box, it'll pull the then-current info off your source sheet. What this DOES NOT DO, though, is make your output static. For example, if someone changes the source sheet from 16 to 17 hours, if the line 5 box is checked, it would also update to 17. There's also nothing to prevent that value from again being masked if someone UNchecks the box. With these caveats added though:

=IF(Deliverables@row=1,INDEX({Total Hrs}, MATCH([Doc Number]@row, {Doc/Dwg Number},0),"")

Note that this includes references - so just copying and pasting it won't solve your issue. You'll need to also designate those references. This also presumes that the doc number columns on both your source data and output sheets are unique - if they are not, it'll result in inconsistent results.

You may also wish to investigate the premium app Data Mesh, which can also do this with additional functionality.

Whatever you decide to do, good luck!

Sandy Drew

You can create column formulas in your Document Deliverables sheet for the Hrs and Rate columns as below.

=IF(Deliverables@row = 1, INDEX({Total Hrs}, MATCH([Document Title]@row, {Doc/Dwg Title}, 0)))

=IF(Deliverables@row = 1, INDEX({Total Chrg}, MATCH([Document Title]@row, {Doc/Dwg Title}, 0)))

KempenUSA

@Kerry St. Thomas Thx Kerry, I will also look into the Data Mesh app.

Laurie Olson

Your column formula for Deliverables Hrs in Document Deliverables is:

=IF(Deliverables@row = true, INDEX({Chgs Sheet - TTL Hrs}, 1, 3))

Your column formula for Charge Rate in Document Deliverables is:

=IF(Deliverables@row = true, INDEX({Chgs Sheet - TTL Chrg}, 1, 4))

These are created in my sheet, so paste the formulas. They will err off. Highlight for each the values between the brackets {Chgs Sheet - TTL Hrs}, then select to edit. It will take you to a navigator to find your other sheet. Select that sheet, then highlight from the column heading the first three columns for the first formula, and all four columns for the second charge rate formula. Once you have updated this reference, you can change the reference name in the top left corner. Once entered, you will return to your sheet, then the formula should work. You need to update both formulas for your specific sheet reference.

Laurie Olson

This is my result in action:

Laurie Olson

Click on Update reference in the lower right corner. You will select all four columns for the second reference, and note that I gave each a unique sheet reference name to easily identify the unique references for each value to bring back.

KempenUSA

@Laurie Olson Hi Laurie, much appreciate your help, unfortunately, and this is my fault as I should have mentioned this, the key is that the response should be based on the document number. Therefore the formula, I believe, should include a index match? Meaning the value based on the -CB-07 as document number.

KempenUSA

@Sandy Drew PERFECT, Thanks Sandy, that worked.

Much appreciated.