📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Spreadsheet help needed.

In order to work out how much time I spend on certain tasks, I have created a spreadsheet.
The columns are 'Date', 'Task', 'Start time', 'End time', 'Total time', 'Task type' and 'Notes'.

The 'Task type' column has 4 options - 'V', 'A', 'D' and 'F'.

Want I want to do is to transfer data automatically onto a second sheet. This sheet would do the following -

Filter by date, Task type and total time for each task type and put the relevant total times into columns marked 'V', 'A', 'D' and 'F'. Thus giving a day-by-day report on how much time is spent on each type of task.

I have the front sheet working perfectly, but just cannot figure out how to auto-fill the second.

Any ideas?.
Never Knowingly Understood.

Member #1 of £1,000 challenge - £13.74/ £1000 (that's 1.374%)

3-6 month EF £0/£3600 (that's 0 days worth)

«1

Comments

  • Try a Pivot Table
  • patman99
    patman99 Posts: 8,532
    Part of the Furniture 1,000 Posts Combo Breaker Photogenic
    Forumite
    A what ?...
    Never Knowingly Understood.

    Member #1 of £1,000 challenge - £13.74/ £1000 (that's 1.374%)

    3-6 month EF £0/£3600 (that's 0 days worth)

  • Have read - http://en.wikipedia.org/wiki/Pivot_table#Explanation_of_a_pivot_table - although if you're not familiar with them (have read of Excel's Help files as well), you might find them a bit fiddly at first. But it will do exactly what you asked for.
  • tonymc
    tonymc Posts: 256
    Part of the Furniture 100 Posts Name Dropper Combo Breaker
    Forumite
    An alternative may be to use the DSUM function which lets you add numbers together that meet a condition you specify. Effectively it sounds as if your first sheet would be the database, then you could simply add four rows at the bottom with the conditions V, A, D & F, and calculate the hours relating to each.
  • real1314
    real1314 Posts: 4,432 Forumite
    SUMIF function is probably the simplest way
  • f1charlie
    f1charlie Posts: 1,228 Forumite
    real1314 wrote: »
    SUMIF function is probably the simplest way

    But with two variables (date and task type) you are looking at an array formula, which doesn't make it so simple (for me anyway).

    I agree that a pivot table is the easiest (once you've got the hang of pivot tables).
    Charlie
  • NeilF3485
    NeilF3485 Posts: 600 Forumite
    f1charlie wrote: »
    But with two variables (date and task type) you are looking at an array formula, which doesn't make it so simple (for me anyway).

    I agree that a pivot table is the easiest (once you've got the hang of pivot tables).

    I think it was new to Excel 2010, although may exist in 2007 as well, multiple variables is no longer an issue:

    You can use =sumifs instead of =sumif
    "We can all fly as high as the dreams we dare to live...........unless we are a chicken" ~ Anon.
  • f1charlie
    f1charlie Posts: 1,228 Forumite
    NeilF3485 wrote: »
    I think it was new to Excel 2010, although may exist in 2007 as well, multiple variables is no longer an issue:

    You can use =sumifs instead of =sumif

    Just moved to 2010 recently from 2003, so that's useful thanks.
    Charlie
  • patman99
    patman99 Posts: 8,532
    Part of the Furniture 1,000 Posts Combo Breaker Photogenic
    Forumite
    Using 2003 at work, so pivot tables may be out of the equation (no pun intended).

    The data from the spreadsheet is going to be used by my line manager and the production manageress to present a case to the plant MD to prove that both my job and that of my new right-hand man are vital to the running of the plant and should be made permanent.

    Our MD likes his charts and graphs, hence trying to impress him.
    Never Knowingly Understood.

    Member #1 of £1,000 challenge - £13.74/ £1000 (that's 1.374%)

    3-6 month EF £0/£3600 (that's 0 days worth)

  • f1charlie
    f1charlie Posts: 1,228 Forumite
    Pivot tables are in 2003 and you can also produce pivot charts from them.

    If you want help in setting it up PM me. You can send me a copy - you can take out any 'sensitive' data.
    Charlie
This discussion has been closed.
★ ★ ★ Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 356K Banking & Borrowing
  • 255K Reduce Debt & Boost Income
  • 456.3K Spending & Discounts
  • 248.5K Work, Benefits & Business
  • 606.2K Mortgages, Homes & Bills
  • 179.1K Life & Family
  • 264K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.2K Discuss & Feedback
  • 37.7K Read-Only Boards
Quick Search

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.