Duplicate Data when a checkbox is checked.

Edzila

New Member
Joined
Jul 12, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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)

Annotation 2020-07-12 145348.jpg
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,796
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.

1594590297547.png
 

Edzila

New Member
Joined
Jul 12, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Have you tried opening it in Excel Online?
I'm not sure that checkboxes work with the online version.
 

Edzila

New Member
Joined
Jul 12, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Why not just put an X in the cell?
 

Edzila

New Member
Joined
Jul 12, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Something along the lines of
=IF(E3="X",D3,"")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 12, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
lol. **** it seems so obvious now... Thanks so much
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top