How do I move daily data from one sheet to a 'yearly sheet'

[ClintH2007]

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.

 

[JLatham]

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]

Hmmm,
How would I create a macro to determine what the current 'date/entrydata' is
so that it copies it? I'm still not sure it would work.

If I would have brought my work home (only have a much older copy here), I
would've pasted the current formula line.

If I could stick the formula into a macro, it would make things a lot
easier, as I wouldn't have multiple formulas looking at my data at once, then
using a macro to 'try' to grab the valid one. With the formula in the macro,
I would create only 1 valid point to copy. Not sure if it's possible.

 

[JLatham]

I replied to the email you sent. Basically the macro would be run at the
click of a button on the data entry sheet when you're ready to move the data
off into one of the collection sheets. It would get the date and equipment
item name from the data entry sheet along with the data points and go hunt
down the proper collection sheet (a scheme we'd have to figure out: how to
match up equipment to collections sheets, but I'll bet you already have
something set up that will work), find the proper date on that sheet and
'type'/copy the data points into it on the date row. All done in the blink
of an eye and no formulas involved on the worksheets at all to get the job
done. That should reduce the size of the workbook, as I suspect you have 356
(365?) * 6 formulas on each data collection sheet. This reduces that to
zero, other than any formula used to fill the date column in on those sheets.