generate static date and serial number when data is entered in any of three cells in row

[mr.chris.strange]

I'm building a spreadsheet template to keep track of lab samples and in-processing analysis data in a waste water treatment facility. I'll be getting potentially 200-300 samples per week, so I need to be able to enter data as quickly as possible. I'd like to be able to enter data in any of columns D, E, or F and have the date and current time generated in A and a serial number generated in B. I would like for the date to be static and formatted as:

yyyy-mm-dd-hh-mm;@

The serial number is different for each sheet and follows the format

XXX-yymmdd-xx

The first three letters will be the first letter of the company or plant name, city, and state, and the final two numbers will be sequential starting with 01. If these can be generated, it will save me some effort, even if they're simply added as a different prefix for each sheet. Each row will have a different serial number and potentially a different date/time.

I'm decent with excel formulas and such, but I'm no programmer and I don't have time to learn how to do this myself. I know I can format the date column, but if it can be included in the code that would make it easier to share this template with my colleagues. I'd greatly appreciate a template of the code to do this, which I can then edit as needed with the appropriate letters, formats, etc. I've attached a part of the workbook I've come up with that includes sample data and all required fields.

[walruseggman]

Here you go. This belongs in the ThisWorkbook object.

If you can come up with a rule of how to get the XXX (GSA, etc) then we can write that into the code. Otherwise they all need to be hardcoded as the two from your example are now.

Please Login or Register  to view this content.

[jaslake]

Hi mr.chris.strange

Welcome to the Forum!!!

I think you may be well served by using a User Input Form to add the Samples to the Data Forms.

Just noodling...questions come to mind...there will be others...

Will your Sheet Names actually be "like" George's - Cassville and George's - Springdale? Will there be more than just these two?

Do you really want the Header Rows repeated between each Days Samples?

Will the User be entering the Samples in Batches and KNOW when she/he is finished for the day such that the Averages can be calculated for that Batch?

[mr.chris.strange]

walruseggman,

That code works perfectly! Thank you so much for your timely response.

jaslake,

I'm not sure what you mean by a User Input Form. Were you thinking of an Access database with a form that feeds into this spreadsheet? That would definitely be beneficial when I'm not the one entering data, but I wouldn't know how to do it. For now I'm the only one that'll be entering data, though, so just using the spreadsheet will be sufficient.

I haven't settled on the exact format for the spreadsheet yet. It will be viewed by people other than myself, so I'll need to make it user friendly. If it were just for my record keeping it would stay exactly as it is now. If I could have a database take data, then send it to a spreadsheet which contains several sheets (raw data, per-analysis data, a summary giving only the output of each type of analysis) which will then run basic calculations and print a response into, say, another database form so I can print it, this would free up my time for more demanding tasks.

So I'm not sure if, in the end, the sheets will still be named as they are. I should think so, since I need to have each plant from which I receive material in a different sheet to allow me to run quantitative analyses. At least, that's how I thought it would be easiest to make. As for the headers - those were only included for readability. I don't need them to repeat in a sheet in which calculations are performed.

The plant will operate around the clock 6 days a week, so the averages will be calculated for a 24 hour period. Could a daily average be automatically calculated each time the spreadsheet is opened for the previous 24 hour period and sent to a cell in another sheet?


I'm not familiar with the protocol for this forum, so should I keep this thread open or mark it as solved and post a new thread with my new questions?

[jaslake]

Hi chris

I'd suggest, if what you have works, don't fix it. Perhaps I was over thinking the issue.