Macro to find matching date and copy values to another sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...
4-Jul-07
Start End time
4 5 9
Click to expand...

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

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