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

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

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

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
---