Hi,
I receive a spreasheet each month up to 5k lines and want to create an automatic summary without making any changes, just inserting the new data each month. My front sheet would automatically summarise.
This should be so simple but I keep getting it wrong.
Your sample contains little by way of explanation and no expected results. One guess:
=IFERROR(SUBSTITUTE(INDEX('P13 Backing Data 250319-210419'!B:B,AGGREGATE(15,6,ROW('P13 Backing Data 250319-210419'!$B$2:$B$25)/(ISNUMBER(SEARCH("Total",'P13 Backing Data 250319-210419'!$B$2:$B$25))),ROWS(G$2:G2)))," Total",""),"")
in G2, copied down... and
=IF(G2="","",SUMIF('P13 Backing Data 250319-210419'!B:B,G2,'P13 Backing Data 250319-210419'!L:L))
in H2, copied down.
If not, please amend your sample to show your expected results.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks Glenn, sorry my information was not as comprehensive as it should have been.
A little more detail. Backing sheet will contain individual line items for each customer (last report 5k+lines) and will be different for each report.
My software kicks out the backing sheet but I need a one line summary for each customer on a new sheet.
That detail would be the customer name and the total value.
I have added more detail to the spreadsheet which I hope will help
Hope that is clearer
My previous formula worked perfectly... (yellow cells), but if you want to derive it from the total (and get exactly the same answer), refer to the green cells.
Hello again Glenn,
Once again my apologies for lack of knowledge and ability on excel.
Your suggestions work well on the small file. I have tried transferring to my working file without success.
If you still have the patience for one more crack I would appreciate it.
I have uploaded a different file with a little more data. So if I can explain.
Data is the report I receive daily/weekly/monthly and varies each report and new sites can be added.
On the previous spreadsheet this was P13 Backing Data 250319-210419, I thought renaming Data might make it a bit easier.
Front sheet is the information and format that I am looking to generate automatically.
This is the information highlighted in Grey on the DATA Spreadsheet (I do this manually 5452 lines of data)
Summary is sheet for Macro's or formula to generate report automatically
I hope that makes sense.
Thanks again.
I like to create tables; allowing dynamic updating as you go & if you are summarizing the same data each and every time create summary sheets from the tabulated data. I feel summary sheets are better when you want the same info from a dynamic data set. If you want random queries then I feel pivot tables are best.
If it's been helpful please mark as helpful
The formulae were linked to your real file, which didn't help... and you had made mistakes in transposing both formulae. Now fixed.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Re: Summary sheet ? Pivot Table
Thanks Glenn, that solved it.
This will save so much time and furthers my rather poor excel education.
Until next time. Thanks again
Experience is not making the same mistake twice...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks