Move cell data to another worksheet dependent on checkbox being ticked

Hi All,

I have been working on the following for three days solid and still cant figure it out!

On Worksheet 1 (RISK CHECKLIST) I have a list with checkboxes assigned to each row in Column B. If the checkbox in Column B is ticked I want it to copy the corresponding number in Column A of worksheet 1, and paste it in column A Worksheet 2 (called RISK ASSESSMENT)

I still need to enter the checkboxes in Worksheet 1 B12 onwards but Ill try and explain a different way

Worksheet 1 - B9 AND B11 boxes ticked, copies data in A9 and A11
Worksheet 2 - Pastes the above into A8 and A9

If anyone can help me with this I will be forever grateful (and will sleep better :-))

Thanks a million

Re: Move cell data to another worksheet dependent on checkbox being ticked

Hello Carim,

I do not mind AT ALL! Thank you so much for responding Anything to help me sort this out. I didn't even think that merging cells may have caused problems (shows how clueless I am! :-))

I will try and attach an unmerged version now

Re: Move cell data to another worksheet dependent on checkbox being ticked

Thanks so much for responding again!

I am setting this up for people who are even more clueless than me with Excel (if thats possible) so I could work with a Yes / No (but just thought it would be easier with a checkbox for them!)

Basically the info pulled over from worksheet 1 to worksheet 2 will have a VLOOKUP set up from worksheet 3

For example:-

If from worksheet 1 checkboxes 1.1, 4.1, 5.1 etc were checked, these numbers would pull over to worksheet 2 (Column A) then Ill set up a VLOOKUP to pull the corresponding risk info from worksheet 3 into worksheet 2 (column B to L)

This was my plan anyway!

Re: Move cell data to another worksheet dependent on checkbox being ticked

Hi Carim,

It would be quite repetitive. Ill try to give you more detail (without sending you to sleep! :-))

Current Process
Please see attached generic Risk Assessment. The current process is the contract manager puts an X next to each risk associated with the work on the first Worksheet and then they are meant to go through all the other worksheets and delete the risks that arent associated with the work (ie do not have an X next to them on the first worksheet) This in itself is time consuming, however most of the Contract Managers dont bother - they just put an X next to the risks involved on Worksheet 1, then send the WHOLE document out to the customer (all 80+ pages and expect the client to match up the risks ticked on the front sheet!)

Dream Process
The Contract Manager would tick the appropriate risk on Worksheet one, The risk number would pull through to Column A on worksheet 2, and then a VLOOKUP would look through ALL the risks (which I would put in one big worksheet in Worksheet 3, rather than 30 off worksheets!) and pull through the info relating to the risk number into worksheet 2

I really hope that makes sense and wish to thank you a million times over for your patience and assistance You are fabulous!!

Re: Move cell data to another worksheet dependent on checkbox being ticked

Hi Carim,

Yes that is correct - the first worksheet is the starting point (a checklist that needs to identify the risks associated to the work) this is completed and sent out to the customer in addition to the actual risks identified

For example on the Current Risk Assessment spreadsheet I sent through, the risks could be 1.1 from Worksheet 1, only 2.3 from worksheet 2 (not 2.1 or 2.3), 9.4 (but not any others) from worksheet 9 and so forth.

I dont know the easiest way of doing this but I basically want the summary sheet still on worksheet 1, the list of risks identified on worksheet 2 (no matter what "category" or worksheet they currently belong to) and anything else not checked on Worksheet 1 deleted.

I never thought it would be so difficult to explain! Thats why I thought a VLOOKUP would work (and I actually know how to do that! :-)) Myinitial thought was to "tick" the relevant risk on worksheet 1, pull the "Risk Number" associated to Worksheet 2, then VLOOKUP the risk number from Worksheet 3 to pull all the rest of the info together

But i am genuinely open to ANY ideas (and appreciate Im not the best at explaining it!

I'm hitting day 4 of sending myself a little insane with this now lol!!

Thanks for everything

Re: Move cell data to another worksheet dependent on checkbox being ticked

Hi Carim,

If you can write me a draft proposal and resolve my headache with this I will be in your debt forever!! I would probably write you into my will

You have been so patient with me and I am extremely grateful for all your help

Re: Move cell data to another worksheet dependent on checkbox being ticked

Carim,

You guess correct - I am impatient!!

That works very well apart from the following:-

1) I cannot double click to get an X (although I can type an X and press prepare report and it does hide all other cells on worksheet 1 that have not been selected so its not really a problem

My next question is how would I get all the hyperlinked information (ie risk linked to B9, B11, B12) to appear in worksheet 2 for example as one long list of "risks"?

I cant believe how clever you are!! I think it will take me 100 years to understand this

Re: Move cell data to another worksheet dependent on checkbox being ticked

Hi Wonderful Carim,

It works!!! I cannot believe how amazing and clever you are - I feel very lucky and am extremely grateful to you for all your work!

Thankyou a million times over!

Re: Move cell data to another worksheet dependent on checkbox being ticked

Hello again Carim,

I've broken all your hard work - I am so sorry!

I look after two companies - therefore two Risk Assessments. I though it would be best if I pulled through all the Risks (for both companies) into one spreadsheet (Worksheet 1), and added the new risks to each page and now it doesn't seem to work as perfectly as you made it work!

I started to highlight the new Risks I had copied from another worksheet in Red (but gave up towards the end!) Please find the revised Risk Assessment attached and I owe you a thousand apologies for it not working as it did before!