I'm thinking that you could come up with a macro that copies the information
to the appropriate sheet and then does the code equivalent of Edit | Copy
followed by Edit | Paste Special with Values checked. That would change your
formulas on the collection sheets to their values for that date - keeping it
from disappearing when the date is changed on the data entry sheet.

But I'm thinking that you have multiple data collection sheets, so you have
to figure out how to determine/force move to the proper sheet.

If it were me, I'd probably write a single macro to do the job, determining
what data collection sheet to use from your A3 cell and then just copying the
information to the appropriate date row on the related data collection sheet.

If this seems to be a course you'd like to pursue, feel free to contact me
at (remove spaces)
HelpFrom @ jlathamsite.com



"ClintH2007" wrote:

I have a DataEntry sheet that allows for the entry of the date, and 6 points
of data (2 columns of 3) for each piece of equipment. Every day, someone
tests the devices and records all 6 points of data on the DataEntry sheet.
This is done for each piece of equipment that requires a test.

My problem lies in that I can not figure out how to copy the data from the
DataEntry sheet (6 points) and place the data in the appropriate row of the
DataCollection sheet (organized by date (1-356 rows)) without the spreadsheet
erasing/moving the data when the date is changed on the DataEntry sheet.
Also, if the data is changed, it will automatically change the data points
sitting in the Collection sheet for that day.

Part of the problem is that I ended up using a formula on the DataCollection
page that checks to see if the date on the DataEntry sheet is the same as the
row, if it is AND there is data in the DataEntry points, it copies it. Each
of the DataCollection columns has the equation to correspond to the rows on
the DataEntry sheet.

I thought about trying making seperate rows/columns on the DataCollection
sheet and moving the formulas to them (so I could hide them) and then use a
macro to copy/paste values when done, but problems would occur.

i.e. the macro would need to do the whole range of rows which would
inevitably cause cells not matching the date criteria to be wiped
AND if someone wants to enter the next days data, they would have to
clear/delete the previous info, which would create a circular problem with
the macro - copies all data (including the now cleared ones=data loss)

The sheets look something like this:
DataEntry Sheet
A2: date you enter
A3: equipment (static) - so I can pull/push this to the appropriate
collection tab
A4: testing device (static) - so I can pull/push this to the corresponding
section of the collection tab for the equipment listed in A3
A6: datapto1 ; B6: dataptc1
A7: datapto2 ; B7: dataptc2
A8: datapto3 ; B8: dataptc3

DataCollection sheet (for any pce of equiment)
A1: datapto1 ; B1: datapto2 ; C1: datapto3 ; D1:date (static to 356 row)
; E1: dataptc1 ; F1:dataptc2 ; G1:dataptc3

Any help would be much appreciated. I have been at this off/on for over a
week.