Hello Everyone,
Long time reader. I love the site. I work at a hospital and have written a spreadsheet to bring up pending lab tests not done on our patients. The spreadsheet connects to our servers through an ODBC and loads the pending tests. It looks loosely like this
Accession Number Container Number Name Test Time Location OUR TIME
W3432 L232656452 Mr. Burns K 1000 CP !!!!
Obviously there is more than just Mr. Burns. I have written a program to update every five minutes. It erases all the data except the headers, loads the patients again, and then does some formatting (column width, etc).
I have 2 problems.
(1) The time (1000) is not representative of the time that we received the sample. I need to add a function that brings up the time that the spreadsheet currently has that populates under OUR TIME. I was thinking =Now(). But how would I have that for every sample in the list since every time the list updates the number changes?
(2) This is the real tricky part. Every time the connection refreshes, it deletes everything then starts over. The OUR TIME would constantly change to whatever time the spread sheet updated. Here is what I need, something like this:
The coding that fines the last line of samples and updates at the first blank line afterwards.
Next the code compares between the original samples that were there and the update to see if there are duplicates and if there are it deletes the duplicate with newest time and keeps the oldest because the oldest is the true time.
All this isn't bad but here is the real issue. We know the sample is done when it disappears AFTER an update. If we refresh WITHOUT deleting everything first, how will we know when the sample is done? I am thinking somehow writing a code that says that if the original set of samples doesn't find a match with the update then delete the original. This would work well if there was a code that would effectively say if columns A through L match (and the OUR TIME is in M) between rows then delete the row with the later time of row M, but if there is no match delete the original row.
I hope this makes sense. Please feel free to ask if you have any questions. If you every wanted to write a code that will truly effect peoples lives this is it. This will help us immensely in providing patient care and working to save lives. I know this is one talented forum and I cant wait to see what interesting solutions pop up.
Thank you all!
Newsbee
Long time reader. I love the site. I work at a hospital and have written a spreadsheet to bring up pending lab tests not done on our patients. The spreadsheet connects to our servers through an ODBC and loads the pending tests. It looks loosely like this
Accession Number Container Number Name Test Time Location OUR TIME
W3432 L232656452 Mr. Burns K 1000 CP !!!!
Obviously there is more than just Mr. Burns. I have written a program to update every five minutes. It erases all the data except the headers, loads the patients again, and then does some formatting (column width, etc).
I have 2 problems.
(1) The time (1000) is not representative of the time that we received the sample. I need to add a function that brings up the time that the spreadsheet currently has that populates under OUR TIME. I was thinking =Now(). But how would I have that for every sample in the list since every time the list updates the number changes?
(2) This is the real tricky part. Every time the connection refreshes, it deletes everything then starts over. The OUR TIME would constantly change to whatever time the spread sheet updated. Here is what I need, something like this:
The coding that fines the last line of samples and updates at the first blank line afterwards.
Next the code compares between the original samples that were there and the update to see if there are duplicates and if there are it deletes the duplicate with newest time and keeps the oldest because the oldest is the true time.
All this isn't bad but here is the real issue. We know the sample is done when it disappears AFTER an update. If we refresh WITHOUT deleting everything first, how will we know when the sample is done? I am thinking somehow writing a code that says that if the original set of samples doesn't find a match with the update then delete the original. This would work well if there was a code that would effectively say if columns A through L match (and the OUR TIME is in M) between rows then delete the row with the later time of row M, but if there is no match delete the original row.
I hope this makes sense. Please feel free to ask if you have any questions. If you every wanted to write a code that will truly effect peoples lives this is it. This will help us immensely in providing patient care and working to save lives. I know this is one talented forum and I cant wait to see what interesting solutions pop up.
Thank you all!
Newsbee
