Extracting daily data from a table, dynamically and daily

I have created a simple table of daily staff numbers for different business areas. The table has twelve months worth of staff numbers, with each row of data representing a specific day (date) of the year, and each column a different office area. I want to - on a different worksheet but in the same workbook - display the CURRENT day's staff numbers only (so, for instance, senior management can just check that worksheet for daily numbers across our offices without having to scroll through a huge table of data). Crucially, I want this single-day worksheet to update automatically, based on today's date.

(And, if possible, having below that single current-day row, a seven-day forecast (i.e. the next seven days listed), however, I'll settle for an answer to the above).

Many thanks in advance! (PS - this is my very first post, so apologies if I inadvertently haven't followed correct procedure).

Welcome to the Forum.

It would help if you attached a sample Excel workbook, so we can see how your data is laid out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and you will see the filename listed on screen. Click on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Hi Pete,

Thanks for the feedback, very valuable. I've created and attached a small workbook (EXAMPLE 1) which is very similar (though pared down) to what I'm working towards. Basically, every 24 hours the summary should change (Sheet 2: Daily Summary), to reflect the current day's figures (contained with Sheet 2: Daily Staff Numbers). Any help greatly appreciated!

You can put this formula in C4 of the Summary sheet:

=VLOOKUP($B4,'Daily Staff Numbers'!$B:$I,COLUMNS($B:C),0)

then copy across.

Hope this helps.

Pete

Thanks Pete, much appreciated, and I'm guessing just after midnight is when I'll see if it works! (in dynamically updating, I mean).

You could just type in another date directly to B4 to test it out, and then return to the =TODAY() formula when you are satisfied that it does what you want it to.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Hi,

Alternatively, you can do it using Pivot Tables - please see attached.
With the "Daily" you select the day.
With the forecast you select the next 7 days.
Will this work for you?

Regards

peterrc