Excel Help Needed

zom247 · 25 Sep 2007 at 19:17

At work I have a spreadsheet that contains job titles, current incumbent and report due date. I want to be able to do a query and print it off to find out when peoples reports are due, ie do a print that contains only the data of peoples reports that are due as at 31 August only.

Is this possible? It would impress my boss no end if I could do it

Participant · 25 Sep 2007 at 19:32

Best way is to use a pivot table as it's a constant dynamic analysis. You can also use the auto filter function.

I.e.:

- highlight the data in question
- go data, pivot table
- go to layout and put due date and person in the page field, and count of due in the data field

That's probably how I'd do it anyway. Then you can very quickly change the due date you've selected and re-print the report. Tart up the pivot with shading etc if necessary.

zom247 · 25 Sep 2007 at 19:36

Robbie G said:
Best way is to use a pivot table as it's a constant dynamic analysis. You can also use the auto filter function.

I.e.:

- highlight the data in question
- go data, pivot table
- go to layout and put due date and person in the page field, and count of due in the data field

That's probably how I'd do it anyway. Then you can very quickly change the due date you've selected and re-print the report. Tart up the pivot with shading etc if necessary.

Cheers, I'll give it a try at work tomorrow.

zom247 · 26 Sep 2007 at 08:01

Just tried this but it's not producing the results I want (or I'm not doing it right!). Say I've got 50 names on my sheet, I want to be able to filter and show only the ones that have a report due on 31 August. It's so I can keep a track on which reports are late so I can chase them up.

MTA99 · 26 Sep 2007 at 11:24

Robbie G said:
You can also use the auto filter function.

I'd try this as a first solution.

Go Data->Filter->AutoFilter

Then use the drop down to filter all results for 31 Aug.

Participant · 26 Sep 2007 at 13:13

zom247 said:
Just tried this but it's not producing the results I want (or I'm not doing it right!). Say I've got 50 names on my sheet, I want to be able to filter and show only the ones that have a report due on 31 August. It's so I can keep a track on which reports are late so I can chase them up.

^^ Agreed, autofilter is the simplest way, but if you want to manipulate it extensively then I'd use a pivot.

Have you clicked the pivot drop-down and selected only the boxes that correspond to 31.08.07? I'd press F1 and read up about pivot tables. They're very powerful.

zom247 · 27 Sep 2007 at 12:17

The auto filter option does what I need at the moment. I'll have to do some playing about with pivot tables later on.

Thanks for the help guys.