Duplicate Data when a checkbox is checked.

[Edzila]

Based on the example below... I need to create a formula or function that when I click the YES checkbox the data from 'D' (in the same row) is copies to 'G' and when NO is checked the data from 'D' is copies to H

I'm hoping this can be done without needing to specify the rows because my dataset is 600 rows long and I really don't want to edit 1200+ cells manually.

All help us appreciated. Thanks in advance.

Also note, that ultimately this will be a shared doc in Microsoft 365, so it will be running in Excel Online. (I have created it in Excel 2019)

 

[mrshl9898]

Right click on the boxes and select "Format Control" to assign them to cells, this will give a TRUE when ticked and FALSE when unticked.

Then

G3 = IF(E3,D3,"")
H3 = IF(F3,D3,"")

It'll also leave TRUE or FALSE in the cell, just change the text to white.

 

[Edzila]

Thanks for this, but I'd still need to manually edit 1200+ cells to make this work which is what I'm trying to avoid

 

[Fluff]

Have you tried opening it in Excel Online?
I'm not sure that checkboxes work with the online version.

 

[Edzila]

That seems to be correct, so is there a way to achieve similar functionality with tools that will work online?

 

[Fluff]

Why not just put an X in the cell?

 

[Edzila]

Okay but then how does that automatically copy the data from D to G / H? on each row?

 

[Fluff]

Something along the lines of
=IF(E3="X",D3,"")

 

[Rick Rothstein]

A possible modification to Fluff's suggestion. Remove the checkboxes as Fluff's suggestion would require, and still use an "X" for the indicator, but if Custom Format the cells In your "Yes" and "No" columns to this Type pattern...

"X";"X";"X";"X"

which will put an "X" in the cell no matter what the user enters into that cell, You should also change the cell's Horizontal Alignment to Center as well. Doing this would require changing the formula Fluff suggested in Message #8 to this instead...

=IF(LEN(E3),D3,"")

 

[Edzila]

lol. **** it seems so obvious now... Thanks so much