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,


    If you do not mind ... a basic piece of advice : get rid of all the merged cells ... before going totally sleepless ... :wink:


    You will make your whole life ... a loooooooot easier .... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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


    Well Done ... !!! :smile:


    Regarding all your future plans, are they several other ideas in your bag ... or just to automatically move risk items to the risk worksheet ?


    Are you 100 % sure you need checkboxes ? or would a Yes/No be fine ?


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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


    Since I know absolutely nothing about your domain ... I am wondering if this would be a One-Time-Only exercice ...or something very repetitive ...


    To make life even easier ...we could think of a mouse double-click ... double clicking on any given row would send the entire row to the Risk worksheet ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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 Donna,


    If I understand correctly ... the Current Worksheet is the starting point of Customer Files (kind of a check-list or template)


    1. Each Family of Risks has its own detailed worksheet ...


    So when you say


    Quote

    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)


    Do you mean the worksheets themselves should be deleted ? (automatically)


    2. Your first idea is to create a Summary Worksheet for the Risks actually identified ...


    3. Your second idea is to regroup all 32 worksheets (or families) into a single worksheet ...


    From a Customer perspective ... what would be the most logical ???


    If I may ...my recommendation would be :


    Whenever the user has reviewed all items ... all blank rows are deleted as well as their corresponding worksheets ... therefore the Summary File is ready ...


    But you are the decision maker ... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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


    Donna,


    Why don't you take a break ... !!! :wink:


    Do you allow me to design a draft proposal ...


    Don't worry .. we will end up with a smooth solution ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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


    Relax ...and please think about something else ... :flower:


    Will get back to you asap ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

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


    Donna,


    Just guessing you are impatient ... :wink:


    So please find attached Version 0 ... ( I insist 0 ...) ... to play with it ...


    Cheers

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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


    Point 1 ... Why you cannot double-click in Column B (quick double_click ...)


    Point 2 ... To add a Hyperlink : Right Click mouse > Select Hyperlink > Place in This Document (on the left menu...) > Destination (select in the window on the right...) > OK ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

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


    Hello Donna,


    Attached is your Version 1 which you should consider as your starting point ... :wink:


    Hope we are heading in the right direction ...

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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 Donna,


    Quite pleased to hear your initial headache might be gone ... :wink:


    Should you feel like improving on the draft version ... feel free to come back to the Forum ...


    Thanks a lot ... for your very kind words ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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! :-(

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


    Hello Donna,


    Attached is a clean "final" version 1 of your workbook ... :wink:


    Question is ... from that point onward ...what are the modifications you would like to incorporate ...?

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)