Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I move daily data from one sheet to a 'yearly sheet'
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I move daily data from one sheet to a 'yearly sheet'
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I move daily data from one sheet to a 'yearly sheet'
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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I move daily data from one sheet to a 'yearly sheet'
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. "ClintH2007" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move data to new sheet - rename sheet based on criteria ? | Excel Discussion (Misc queries) | |||
move rows of data seperated in a sheet to a sheet with no separat | Excel Worksheet Functions | |||
Move data from a sheet to another | Excel Discussion (Misc queries) | |||
How to move data from one sheet to another | Excel Discussion (Misc queries) | |||
I need a daily sales sheet to pull from monthly figures sheet | Excel Worksheet Functions |