Data Entry duplicated in chronological order

**kosti** · 01-28-2014, 03:08 AM

First of all, I need to thank the people of this forum for helping me out, without knowing it, over the past few months. I've gotten answers to numerous questions from this forum, often with a simple search (sometimes a complex search). Finally a time has come where I can't seem to find an answer, likely because I don't even know how to search for it. So I'm making my first post in hopes of there being a solution out there somewhere.

Here is my issue in a nutshell, I am trying to figure out a formula to do the following:

Anytime I input a value into one section of a worksheet, the value entered must be duplicated into two other, separate locations AND must duplicate in order of input....here is a picture to better understand what I'm looking at.

sample1.jpg

I need to input data into the table on the right, and I need the values that I input to duplicate into the proper sections in the specific tables on the left. Furthermore, I need them entered in the specific tables on the left in order of me inputting them as they will be input on different days and need to keep in this order.
So for example...I input data into Y2 and Z2 (this corresponds with Test 1 Row and Test 3 column). I need the data that I just entered into Y2 to go to A2 and Q2 as well...the data entered into Z2 to go to B2 and P2 as well. The next set of data that comes in is input into Y6 and Z6 (this corresponds with Test 5 Row and Test 3 column). Those values need to be duplicated as above, however this will be a second entry for the Test 3 column, meaning it will need to go underneath into P3 and Q3 appropriately.
The problem is I don't know which data will be input at what time, meaning that I need to somehow list the entries in the specific tables on the left in order of input and I have no idea how.

Any help would be very much appreciated...not sure if there is even anything out there for this but if so it is well beyond my general knowledge.

**Kaper** · 01-28-2014, 07:35 AM

you probably shall write a code for worksheet Change event handler.
there are thousands of examples on the subforum: http://www.excelforum.com/excel-programming-vba-macros/
As a long time user, you probably (or may be - not) are aware about section "Want to get your question answered quickly?" in http://www.excelforum.com/forum-rule...rum-rules.html
Here - "Post a workbook" applies very well.

Your macro will probably look like :

Please Login or Register  to view this content.

**kosti** · 01-28-2014, 10:18 AM

Kaper,

Thank you for your response. I've never used code before and was hoping to stay away from it for that reason. I will take a read through the link that you posted and the macro you attached. I will also post a workbook and I apologize for not doing that in the first place, should've read the rules first.

On an aside, I see that you are from Warsaw....my entire family is from Krakow and will be visiting there second week of March for a few weeks!

TestBook.xlsx

**Kaper** · 01-29-2014, 07:27 AM

Hi kosti,

Your data layout is for sure not "one of the easiest to work with", so may be it is not a good piece of code to make your first steps with VBA.
Anyway, see such a proposition of code (also working in attachment)
Aside from main code I added checking for several exceptions
- some not really expected (like not enough space in second column)
- or more probable - like copying something into several cells at once - for instance U4:X7

The code with comments looks now as follows:

Please Login or Register  to view this content.

As I said - it would be much more strightforward (so a good piece to start developing programming skills) your if you would have for instance all 12 pairs for each test in one column and all test one by one in row or in column.
I mean setup like the one shown for reference in the picture.

Anyway - the above code works with the desired layout. and the only a bit more complicated part is using integer division (\) operator and mod operator to locate interesting cells location in desired sequence.

Off-topic:
Have a good trip and unforgetable stay in Krakow.
In my opinion an absolute must-see are Old Cloth Market Cellars (opened for the public just a year ago, with superb multimedia tour) and Wieliczka Salt Mine.
Of course hundreds more, just to start with Mariacki Church, Wawel Castle, Old Synagogue, Collegium Maius in Jagiellonian University, Schindler’s Factory, etc., etc.