Date specific formula

[Tall Josh]

Hi Guys,

I hope you can help with the problem that has been puzzling me for days now. Here goes!

I have a table of figures in one sheet with months Jan-Dec and a list of staff. In a seperate sheet I want cells to reference information from the first sheet but I want them to change automatically when each month expires.

So, basically, ideally, sheet 2 will relate to staff member A's stats for January as shown in the first table until the 1st of February, (real time) when it will automatically change to February's figures.

I hope that makes sense and that there is a way to do this!

Many thanks for any advise you can give.

[TMS]

Any chance of a sample workbook?

Regards

[Tall Josh]

Sorry, I'm new to this forum, I'm trying to work out how to give a sample! So, when I work it out, tab 'Figures' is where the info is coming from and tab 'Staff Member 1' cells F9 - F28 is where the info needs to hit. But relevant to real time dates.

Thank you in advance for any advise.

[Tall Josh]

Okay, got it, here it is.

[TMS]

Calls:
=OFFSET(Figures!$A$1,MATCH($L$2,Figures!$A:$A,0)+2,MATCH($F$2,Figures!$A$2:$M$2,0)-1)

Bookings:
=OFFSET(Figures!$A$1,MATCH($L$2,Figures!$A:$A,0)+6,MATCH($F$2,Figures!$A$2:$M$2,0)-1)

Where F2 has the Date: 01/mm/yy
And L2 has the Staff Name/Number


I don't think you're doing yourself any favours with all the blank rows and merged cells.

And the repetition of the headings on the Figures sheet is just a waste of space. IMO it just makes it very hard to read.

Anyway, your design ;-)

You'll also need to adjust the charts to reflect the staff member. Haven't gone there given I don't know if this answers your question or not.

Regards

[Tall Josh]

Thanks for this. I will have a play and let you know.

[Tall Josh]

Hi TMShucks,

I know all the blank cells and merged cells are a little unauthodox but this sheet is to be printed as a monthly hand out so it needs to look nice on paper. I take your point on repetative headings, can't really remember why I set it like that, (maybe a copy and paste job!)

I can't get the formula to work though, I'm sure it's me, but anyway, I will keep playing.

Thank you again

[Tall Josh]

Okay, it does work perfectly, thank you very much.

[TMS]

That's what I like: leave the question floating for an hour and you find the answer yourself ;-)

Thanks for the rep. If your question is now answered, please mark the thread as solved.

Regards