Ready, steady… save!
You don’t need a chart to join in. Here are 2 ways to record your savings:
1. Cross it off
Pop a chart into your first post. Saved an amount? Cross it out! Read more »
£0.01 £0.02 £0.03
2. Write it down
Skip the chart and add updates to your first post. Read more »
“Another +£3 saved today. Go me!”
Use +£ before each new saving so it counts towards your total.
📨 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.
patman99
Posts: 8,532
Forumite
in Techie Stuff
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?.
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)
Member #1 of £1,000 challenge - £13.74/ £1000 (that's 1.374%)
3-6 month EF £0/£3600 (that's 0 days worth)
0
Comments
-
Try a Pivot Table0
-
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)0 -
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.0
-
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.0
-
SUMIF function is probably the simplest way0
-
-
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.0 -
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)0 -
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.Charlie0
This discussion has been closed.
Confirm your email address to Create Threads and Reply
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