Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find matching date and copy values to another sheet
I have 2 spreadsheets, dailydata and datamonth. I need a macro which looks up
the date in dailydata which is in a fixed location, and then finds the matching date in datamonth across a row of monthly dates (e.g. 01 jul to 31 jul) and pastes values from dailydata below the matching date in datamonth. dailydata 3-Jul-07 Start End time 1 7 6 Datamonth Dates 2-Jul-07 3-Jul-07 4-Jul-07 Start Run End Run Run Time Thanks Heaps |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find matching date and copy values to another sheet
A formula might suffice ..
Illustrated in this sample: http://www.flypicture.com/download/MjYwNw== Extract n Transpose data by date into another sht.xls In dailydata, Data is presumed logged like this (below) down cols A to C, with dates in col A, corresponding run-times placed 2 rows below in cols A to C 3-Jul-07 Start End time 1 7 6 4-Jul-07 Start End time 4 5 9 and so on In datamonth, dates are running in B1 across Put in B2: =OFFSET(INDIRECT("dailydata!A"&MATCH(B$1,dailydata !$A:$A,0)+2),,ROWS($1:1)-1,) Copy B2 down to B4, fill across as far as required Perhaps slightly better with a simple IF check on the dates in B1 across, you could use in B2: =IF(B$1="","",OFFSET(INDIRECT("dailydata!A"&MATCH( B$1,dailydata!$A:$A,0)+2),,ROWS($1:1)-1,)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tiger" wrote: I have 2 spreadsheets, dailydata and datamonth. I need a macro which looks up the date in dailydata which is in a fixed location, and then finds the matching date in datamonth across a row of monthly dates (e.g. 01 jul to 31 jul) and pastes values from dailydata below the matching date in datamonth. dailydata 3-Jul-07 Start End time 1 7 6 Datamonth Dates 2-Jul-07 3-Jul-07 4-Jul-07 Start Run End Run Run Time Thanks Heaps |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find matching date and copy values to another sheet
in Max, many thanks, the formula does work, however the problem is that I
need to retain the data, when I change the date it updates the cells for that date but does not keep the data for the previous day. Is there a way to fix this? Note also need to retain data as detailed below. Dailydata will have new values for each day. The update needs to read and paste as a value only into datamonth, it cant copy the cell as the formula in the cell will be a problem . Also there are some blank cells between some of the info in dailydata so how do you handle this? e.g info cells A3-C3, then F3, then H3-L3 My thought was to have a macro that found the matching date from dailydata in monthdata, and tthen using a series of range copies to trasfer the data to monhtdata. I have tried but unsure on how to code the if loop, open to suggestions, can email spreadsheets if that helps, thanks "Max" wrote: A formula might suffice .. Illustrated in this sample: http://www.flypicture.com/download/MjYwNw== Extract n Transpose data by date into another sht.xls In dailydata, Data is presumed logged like this (below) down cols A to C, with dates in col A, corresponding run-times placed 2 rows below in cols A to C 3-Jul-07 Start End time 1 7 6 4-Jul-07 Start End time 4 5 9 and so on In datamonth, dates are running in B1 across Put in B2: =OFFSET(INDIRECT("dailydata!A"&MATCH(B$1,dailydata !$A:$A,0)+2),,ROWS($1:1)-1,) Copy B2 down to B4, fill across as far as required Perhaps slightly better with a simple IF check on the dates in B1 across, you could use in B2: =IF(B$1="","",OFFSET(INDIRECT("dailydata!A"&MATCH( B$1,dailydata!$A:$A,0)+2),,ROWS($1:1)-1,)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tiger" wrote: I have 2 spreadsheets, dailydata and datamonth. I need a macro which looks up the date in dailydata which is in a fixed location, and then finds the matching date in datamonth across a row of monthly dates (e.g. 01 jul to 31 jul) and pastes values from dailydata below the matching date in datamonth. dailydata 3-Jul-07 Start End time 1 7 6 Datamonth Dates 2-Jul-07 3-Jul-07 4-Jul-07 Start Run End Run Run Time Thanks Heaps |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find matching date and copy values to another sheet
Tiger,
You're welcome. Suggest you try posting in .programming. Good luck ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date values change when I copy an excel sheet to a new book | Excel Discussion (Misc queries) | |||
find a cell matching separate column and row values | Excel Worksheet Functions | |||
Copy Macro values to new sheet | New Users to Excel | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Discussion (Misc queries) |