weekly summary sheet Excel
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
weekly summary sheet Excel
I feel like there should be a very simple solution for this, but can't make a pivot table work and wonder if someone can help.
there is a spreadsheet that lists all 365 days where people enter items issued on a daily basis. they would like a second tab to show weekly totals to then track trends. i was trying to use sumifs - but get errors is there a simpler way?
there is a spreadsheet that lists all 365 days where people enter items issued on a daily basis. they would like a second tab to show weekly totals to then track trends. i was trying to use sumifs - but get errors is there a simpler way?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 12608
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: weekly summary sheet Excel
I had a quick look at this workbook, but I can't see any dates, just 100 rows that list #, Item, M/F/Unisex. Minimum stock, Current Qty
StuartR
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
Re: weekly summary sheet Excel
the dates are actually in row 4. the full dates aren't displayed. the weeks are named variables.
-
- Administrator
- Posts: 12608
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: weekly summary sheet Excel
Row 4 certainly doesn't look like dates to me
You do not have the required permissions to view the files attached to this post.
StuartR
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: weekly summary sheet Excel
maybe something like this?
but your description is very poor and not clear
but your description is very poor and not clear
You do not have the required permissions to view the files attached to this post.
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
Re: weekly summary sheet Excel
Sorry. I understand the issue and do apologise! i forget that people can't read my mind!!!!! On a daily basis, staff will issue clothing and update the issued spreadsheet to reflect what they have issued. On the weekly summary tab, they would like to summarise what has been issued on a weekly basis, so they can look for trends to influence future stock ordering as well as to highlight information about service users. Basically they would like a weekly summary for each of the sizes and items of clothing. the pivot table you have created works well, but they have asked for the simplified version as shown in the weekly summary tab. I hope that explains it better. I couldn't find a simplified way to put it in the pivot table.
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
-
- Administrator
- Posts: 12608
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: weekly summary sheet Excel
The workbook that I downloaded from the first post in this thread has only ONE sheet.luv2bounce wrote: ↑09 May 2023, 14:54Sorry - it is row 4 in the issued and weekly summary tabs which are the ones I was interested in. forgive my terrible description of my problem!
StuartR
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: weekly summary sheet Excel
I think you attached the wrong workbook, there is no "issued" tab, nor a "weekly summary" tab.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: weekly summary sheet Excel
move FullYear to the FILTERS area and you will get possibility to choose single week table
note that this is only for one year 2023
would be nice to see example of expected result
note that this is only for one year 2023
would be nice to see example of expected result
You do not have the required permissions to view the files attached to this post.
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
Re: weekly summary sheet Excel
I thought I uploaded the full worksheet - no wonder there is so much confusion! Thank you all for being so amazing in trying to assist.
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
Re: weekly summary sheet Excel
Thank you all for your patience with me!
I think I can make the pivot table work - how are you getting the full year? I'll also try to attach the file with 2 tabs.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: weekly summary sheet Excel
Here is a solution using SUMPRODUCT.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: weekly summary sheet Excel
Power Query Editor:luv2bounce wrote: ↑10 May 2023, 09:18I think I can make the pivot table work - how are you getting the full year?
there is a list of dates from 01/01/2023 to 31/12/2023
Code: Select all
= {Number.From(#date(2023,1,1))..Number.From(#date(2023,12,31))}
-
- 4StarLounger
- Posts: 577
- Joined: 14 Nov 2012, 16:06
Re: weekly summary sheet Excel
You'd better use columns G and H in 'Issued' to store 'date' and 'quantity'.
The better the data sctructure the simpler any analysis.
The better the data sctructure the simpler any analysis.
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
Re: weekly summary sheet Excel
Wonderful, wonderful, wonderful!!!!! I am starting to understand more! The sumprodiuct was the original type of formula I was looking for, but couldn't figure out how to make it work. Thank you so much @Hans for showing me! And Thank you also @hamster adn @smb for your suggestions about structuring and use. I am very, very grateful for your help and patience with me!
I don't have experience with Power Query yet, so will start looking into that to see what I can learn.
Honestly, I am so grateful to you all!!!!!
I don't have experience with Power Query yet, so will start looking into that to see what I can learn.
Honestly, I am so grateful to you all!!!!!
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: weekly summary sheet Excel
see link in my sigluv2bounce wrote: ↑10 May 2023, 15:10I don't have experience with Power Query yet, so will start looking into that to see what I can learn.
-
- StarLounger
- Posts: 59
- Joined: 07 Dec 2010, 12:21
Re: weekly summary sheet Excel
most definitely will! Very grateful for the help!hamster wrote: ↑10 May 2023, 19:25see link in my sigluv2bounce wrote: ↑10 May 2023, 15:10I don't have experience with Power Query yet, so will start looking into that to see what I can learn.