Copy cells to worksheet based on todays date

[heffers1]

hello everybody

this is probably really easy, but I am trying to improve an existing roster planner and need some assistance with a suitable formula .

Essentially, I'm aiming for a main page to display various KPI data, but it also needs to show who will be on which shift

I've got that bit worked out, but need to transfer the shift attendance results to the main page - see simplified example

On the annual page, each day of the year has 15 spaces for each shift, So I need to lookup today's date, and then report the shift members on that date in the appropriate place on the main page.

Grateful for any advice

regards

heffers

[Pete_UK]

Put this formula in C7 of the Dashboard sheet:

=IFERROR(INDEX(Annual!$C$10:$W$24,ROWS($1:1),MATCH($C$2,Annual!$C$9:$W$9,0))&"","")

and this in E7:

=IFERROR(INDEX(Annual!$C$25:$W$39,ROWS($1:1),MATCH($C$2,Annual!$C$9:$W$9,0))&"","")

Then copy down to row 21.

Hope this helps.

Pete

[heffers1]

Pete,

thank you for this reply - it works perfectly on my versions of excel (2016 and 2013) but when uploaded to the final user, (who is still using excel 2007) the results of these formulae do not appear.

should it be backwards compatible or could it be a restriction on the users setup?

regards

heffers

[Pete_UK]

I use Excel 2007, and it works for me. I suspect that the data arrangements on your final user's file is slightly different, so you need to check out the cell references on that file. Also, check that the sheet is actually named "Annual", and not "Annual " (i.e. with a space at the end, or at both ends).

It might help to remove the IFERROR( ... ,"") from around the formula, to see what errors are produced.

Hope this helps.

Pete

[heffers1]

Pete

your advice certainly does assist greatly.

I thought it odd that it works for you and not the end user, so I loaded up a version of 2007 on an old machine and did some checking.

There is no fault with your formula, it is in fact the formula used to populate the data fields on the Annual worksheet - Doh !!

these fields are populated by variations of

=IFERROR(INDEX($B$11:$B$99,AGGREGATE(15,6,ROW(D$11:D$99)-ROW(D$10)/(D$11:D$99=OFFSET($B$115,INT((ROWS($1:1)-1)/15)*15+1,0)),MOD(ROWS($1:1)-1,15)+1)),"")


which provides the data that your formula references to. I suspect this formula needs to be in a format 2007 understands since AGGREGATE didnt exist until 2010.

Would it be easier to extract the early / late data on a date basis directly from the annual sheet into cells on the dashboard page?

Current annual page attached

heffers