Macro Help - Copy paste based on Date

[Steves73]

Hi All

Need some help, apologies if the below a is a bit confusing

So, Need a macro that looks in column B, C, D. Matches the date on the rows 76, 81, 86. then copies the corresponding "Unit No" in Column A to the matched cell in rows 76, 78,86 (and so on) - Calendar will be for a year but got example sake I'm only showing March, April, May

Also, macro will then add to row 79, 84 89 and so on under "Unit No" the corresponding data in either B1,C1,D1,E1

Please note, information in Rows B,C,D and E and populated by Xlookup so automatic

More than happy to reformat to make it easier for the Macro

Any Help is much appreciated

 

[James006]

Hi,
To make your current life ( and future life ...) a lot easier ... you should consider having two worksheets ....

 

[Steves73]

No Problem, I can do that

Want to be able to print the calendar anyhow

 

[Herakles]

What does the data look like that columns B,C,D and E are populated from by Xlookup?

If you had the data in a structure such as date, unit number, service level (the longer and thinner approach)
then you could just do a look up.

 

[Steves73]

=XLOOKUP(1,('PM Schedule'!$B$4:$B$800=Sheet3!A3)*('PM Schedule'!$G$4:$G$800=Sheet3!$B$1),'PM Schedule'!$F$4:$F$800,"")

I have a sheet "PM Schedule" It runs a plan based on Kilometer readings of vehicles., Xlookup matches Vehicle Numbers (LV Number) (I.E LV-102) to a date in Column F and returns the number in Column G. This information is pasted to Sheet 3 and formatted as per my original snipped sheet

 

[Herakles]

This is how I have done it which means that you can split the data and the calendar across
two worksheets. As always there may be a 'better' way but this tested my knowledge.

I have set up a dynamic named range called 'dates' to cover the dates represented in your range B2:D70.
I've also set up a dynamic named range called 'unitno' to cover the Unit No represented in your range A2:A70.

This formula returns the Unit No
=INDEX(unitno,MATCH(1,MMULT(--(dates=H$2),TRANSPOSE(COLUMN(dates)^0)),0))

This formula returns the Service Type
=OFFSET($A$1,0,MATCH(H$2,OFFSET($A$1:$E$1,MATCH(1,MMULT(--(dates=H$2),TRANSPOSE(COLUMN(dates)^0)),0),0),0)-1)

In each case H$2 represents a reference to the date in the calender.

 

[Steves73]

Hi mate, Thank you very much.

I have set the dynamic ranges and named them, bit confused as to what H$2 does?

Also, is the formula to go in B78 and dragged along?

I had planned to get the formula to work on the same sheet, once I understand the references then set the calendar to a separate worksheet

You'll have to forgive me, novice on large formulas

If you can send the cell references as to where the formula needs to go, that would help out a great deal

 

[Herakles]

Yes, B78 and dragged along?

It refers to the cell two rows above which contains the date.

The Service Type formula goes into B79 and then dragged along.

Same goes for the other months. Just make sure that the correct date is being referred to.

Don't try and understand the formulas for now.

I assume that only one unit is serviced each day.

 

[Steves73]

Hi mate

Not sure where Im going wrong

2 pictures below

 

[Steves73]

Range Name