Fill cells with data based on dates?

[tezza]

Hi All,

Hope you can help.

I've created a dynamic calendar but would like it to auto populate based on the dates and initials below it (see image of manually entered data for example purpose).

What is the best way to achieve this please?

Below is the link to the sheet that is in google drive which is view only to keep the original data in place but you should be able to copy it:
Dynamic Calendar

Thank you.
Terry

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[tezza]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hmmm, I did have a signature previously. I use 2010.

 

[Fluff]

Your signature shows 2007, but it's best to update your profile to show your version of Xl.
Unfortunately I'm not sure it's going to be possible to do this with your version of xl, as you don't have textjoin.
How many rows of dates/initials are you realistically likely to have?

 

[tezza]

Your signature shows 2007, but it's best to update your profile to show your version of Xl.
Unfortunately I'm not sure it's going to be possible to do this with your version of xl, as you don't have textjoin.
How many rows of dates/initials are you realistically likely to have?

There's 140 staff that have 28 days holidays so quite a few once it's up and running.

 

[Fluff]

In that case you would be better off with a macro, is that an option?

 

[tezza]

In that case you would be better off with a macro, is that an option?

Yes macros are fine but I'd need one setup for me.

 

[Fluff]

Ok, how about, in a standard module

Function tezza(Dcl As Range, DRng As Range) As String
   Dim Cl As Range
   
   For Each Cl In DRng.Columns(1).Cells
      If Dcl.Value >= Cl.Value And Dcl.Value <= Cl.Offset(, 1).Value Then
         tezza = tezza & "," & Cl.Offset(, 3).Value
      End If
   Next Cl
   tezza = Mid(tezza, 2)
End Function

used like in B19 dragged across

=tezza(B18,$B$36:$F$37)

 

[Fluff]

Thinking about it, this is probably a better formula

=IF(MONTH(B18)<>MONTH($A18),"",tezza(B18,$B$36:$F$37))

in-case holidays cross months

 

[tezza]

Thinking about it, this is probably a better formula

=IF(MONTH(B18)<>MONTH($A18),"",tezza(B18,$B$36:$F$37))

in-case holidays cross months

I've added the VBA and adjusted the cell ref to suit but get #NAME? errors.