Log values entered in a cell single cell via drown menu on sheet1 onto sheet2 in a row

Hi I am new to VBA and I have really been struggling to create a macro that runs smoothly On Sheet1 I have 8 values ranging from 1,2,4,5,-1,-2,-4,-5 that are entered into cell "D2" via VLOOKUP with a drop down menu in "C2". Is it possible that every time I clicked on the drop down menu in "C2" and populate "D2" with 1,2,4,5,-1,-2,-4,-5 that a macro can record on a corresponding row on sheet2 e.g sheet1 click "C2" Populate "D2" with value, Sheet2 "E2" would record the result, next click would be recorded in "F2" and the next would be in "G2" and so on. I know it sounds simple but I am finding it very difficult, I have all the functions working but really struggling here with the macro part and any help would be very much appreciated. I have 150 entries on sheet1 so if you can point me in the right direction I would very happily write out the 150 macros for each row if need be.

In Sheet2 iterations does the "E2", then "F2" then "G2" represent the last columns of information for Sheet2?

I would like the information captured from cell D2 sheet1 to go on indefinitely on sheet2 row 2 so E2, F2, G2 and so on. Is this possible to achieve. I really need the help thanks so much.

Here is the sheet is ain't pretty that's for sure but I thought I could do all that stuff later if I ever was able to get it working. Thank you so much I really appreciate any help I can get.

You Leith Ross are awesome thank you so much!!!!!!!!!!!!!!!! is it possible just to record the value of column D on sheet one on sheet 2 in the corresponding row so the values would go horizontally across the sheet eg 1,2,4,-4 and so on. I ask this cause I was hoping to run a Sum function on the second sheet and have it output to E column on the sheet1 for each individual. Thank you so much I was just mesmerized watching the macro work and seeing it could be done. Many many many thanks in advance.

Forget my post before what you have given me is gold thank you so much I was wondering can you show me how I would enter in a Now function in the macro

Scratch that as well thank you both you I appreciate the interest in my post and I especially appreciate your help Leith Ross.