Hi, in MS Excel I have 10,000 rows of data in 35 columns in Worksheet A, the unique identifier for each row is contained in Column C called the SaleID which is alphanumeric (sometimes an Integer or string).
I have set up in Worksheet B a simple form that acts like a report, extracting data from Worksheet A, primarily using Vlookup, based on any manually entered SaleID in cell A1 of Worksheet B.
I've tried my best at writing some code but it's completely baffling me and can't seem to figure it out...please can you help me with VBA code that works as follows (please follow through with the example at the bottom as well):
1. In Worksheet B, I enter a random SaleID in cell A1;
2. Then when I click on the single checkbox in cell B1 of Worksheet B the code should look at the SaleID in cell A1 and then go to Worksheet A and Column C and find the the associated SaleID for example row 254, and write, and permanently record, in Column D of the same row, either a 'Yes' (i.e., the checkbox is ticked) or a 'No' (i.e., the checkbox is not ticked);
3. Then each time a row in Column D in Worksheet A is updated to 'Yes', Column E, in Worksheet A, permanently records the Date and Time (24hr clock) (in format dd/mm/yyyy hh:mm) of the last update (which is different to =Now() and =Today(), in that these two functions change each time I open the workbook).
4. Lastly, in Column F, of Worksheet A, in each row I would like to calculate in days how much time has expired since the last update in Column E.
WorkSheet A Worksheet B
Row Col. C Col D. Col E. Col. F Col. A Col. B
1 SaleID Listed Updated +/- N09786 'Checkbox'
2 7428 Yes 01/02/2015 22:15 209 days
3 xx6250 No
254 N09786 Yes 20/08/2015 09:38 9 days
I know this is quite challenging and perhaps difficult to program but I've yet to come across any example that someone has been able to do this. Please do let me know if there are any questions.
Thank you in advance for all your help.