Loop through two validation lists + copy and paste output

  • Hi there,


    I'm trying to create a macro to automate calculation outputs for different variables. Very new to macro so looking for guidance.


    In Sheet1, I have 2 dropdown (data validation) lists (one for name in cell A1, one for date in cell A2). I have a pre-built calculation with 2 different outputs in cell B1 and B2. I want to calculate for each name across all the available dates and copy the outputs in cell B1 and B2 to Sheet1 column C and D that match name in column A and date in column B. Here's the logic I'm trying to do:


    1. Select from name list in cell A1 in sheet1
    2. Select from date list in cell A2 in sheet1
    3. Copy value from B1 and B2 from sheet1 to column C and D in sheet2 that match both name and date in column A and B
    4. Select the next date in A2 in sheets and repeat step 3 for all the available dates in the dropdown
    5. Once done, select the next name, repeat all the steps above. Repeat for all the names and dates.


    Any help is greatly appreciated.


    Thank you!

  • Hi and Welcome to the Forum :)


    To make things a lot easier for everyone ... why don't you attach a sample file ...;)

    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...:)

  • Thanks for the sample file ...


    But, have to admit I do not understand what is the final result you are expecting ...


    Could manually fill in some cells with your results ...

    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...:)

  • I could but the there're thousands of rows, and the data will consistently change, so copy and paste isn't very sustainable.


    All I'm trying to do is to automate copying output I and II in sheet1 to the sheet2 for each name across all dates. The outputs you currently see are static, but in my actual files, they are different numbers based on a calculation we've built and name/date selected from data validation.

  • Also, I know it can be confusing. The reason why I can't just do a lookup is because the sample outputs I shared in D7:G13 don't actually exist in the sheet. They are the background calculation hence why I'm trying to automate copying B1 and B2 outputs.

  • Quite honestly ... Unless you clearly and specifically explain :


    On one hand :


    1. What is the starting point .... the Input Data .... The Source


    and, on the second hand :


    2. What is the final point ... the Output Data ... The Destination


    There is no way I will be in a position to help you out ...


    Another more talented contributor will certainly be able to decipher your explanation ...

    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...:)

  • Well ... believe or not ...


    Think understood the first step ...


    With the NameDV worksheet and the DateDV worksheet, create the First Two Columns of your Sheet 2 ( The Output Sheet )


    If this is fine ... still need to understand how your two values located in Sheet1 : Value I ( in cell B1 ) and Value II ( in cell B2 ) have to be copied back to the last Two Columns of the Output Sheet ...


    Can you describe the logic ... ???

    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...:)

  • I'm not sure I understand your question.


    I think, essentially, the values (depending on name and date selected) will be copied to column C and D in sheet2 based on the matching names and dates in first 2 columns of sheet2. So value I for name A and date 1/1/2021 will be copied to cell C2 of sheet2, and value 2 for name A and date 1/1/2021 will be copied to cell D2 of sheet2.

  • What do cells B1 and B2 contain ...???


    Sophisticated formulas which will produce a different outcome... each and every time .... a new selection is made in either cell A1 or cell A2 ... ( in each of these cells, there is a data validation with a number n of possibilies ...)


    So you need to produce the total expansion of all potential combinations ... with both Inputs and Outputs ...


    Is that right ?

    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...:)

  • Got it ...!!!


    Pleased to have reached .... the starting line ... 8o


    As soon as I have some time ... will dive into your request ...

    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...:)

  • A true relief to see the logical path the macro will have to follow to generate the global Output ...


    A couple of remarks regarding your specific functions in cells B1 and B2 :


    1. The logic the macro has to follow will not be affected by these functions

    But

    2. The actual real life application could be significantly slowed down, depending on the complexity of these formulas ...;)

    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...:)