Log In
Register
Home
Active Member
May 25, 2024
Offline
Good Evening,
I got some invoices. My Boss wants to see Months Horizontally. I want it to see the correct numbers whether it is Horizontal or Vertical. Right now, it is working fine as far as Horizontal Sum is concerned but vertically It is not giving correct results. I just created some range with dummy data. I can’t even covert it to a table since my Real Data is very messy. Little bit i have shown in this Dummy Data. Data is in Blocks and there are Totals after some blocks and some don’t have any Totals. Also, Some Totals are there but we are not adding them at the Final Total.
I have spent whole day but somehow it is not working. I want it to be Dynamic since now Next Month I will get new Invoices and I will just paste Quantity and Amount in in Sept Columns and I want that last 2 columns update automatically whether Horizontally or Vertically.
Can you please help?
Thanks,
Pam
July 16, 2010
Offline
Hi Pam,
Please share your file so we can help you.
Mynda
Trusted Members
October 17, 2018
Offline
Good day, I also suggest you post the Excel version you're using
Active Member
May 25, 2024
Offline
Good Morning, Mynda
Thanks! I did upload my Screenshot last time but I got an error. May be it didn't upload. In our office we are using 365.
Thanks,
Pam
July 16, 2010
Offline
Hi Pam,
Thanks for sharing your file.
The best way to automate the aggregation of this data is with Power Query. This spreadsheet as it is will always be a manual process because you can't even copy the horizontal totals across.
Presumably in your proper file you'll have row labels to differentiate each row, so I haven't bothered to provide an example in this file given that it's incomplete. Note: you will also need a way to ignore the totals you currently have for the columns on rows 10, 19, 33 etc. you could do this by putting 'Total' in the row label for those rows.
Please see this Power Query Unpivot tutorial. Example 2 deals with your data layout. Once you've unpivoted the data, you can summarise it with PivotTables and then simply click one button to get updates each month.
I hope that points you in the right direction.
Mynda
1 Guest(s)
