Forum Discussion
Sales figures by day from multiple tabs averaged in to a summary?
kf233 What exactly and why are you putting data in separate sheets? Data analysis becomes much easier if you collect all data in one data table/list. Can you share a file with some example data, by posting a link giving full access to it on OneDrive or similar? Remove any private and confidential information though and specify the results you want to achieve.
- Thanks. The data exports from my ticketing system by event, which is why it's in separate sheets. The system doesn't allow me to pull daily sales reports for all events combined.
Link: https://1drv.ms/x/s!AulXG6vfEpU6_BjjWYnBYSRK3sqY?e=oXGVCH
Overview tab gives an idea of the results I want to achieve. There is likely a cleaner and more efficient way to present the data.
We hold the same events every year. I want to be able to look at my spreadsheet overview and say "we are X days out from the event. From previous years' sales data we've confidently sold X% of the total ticket sales, which means we can expect X total sales" to help with the event planning and budgeting.If you prefer a formula to copy/paste, something like
= VSTACK( EXPAND("",30,2,""), HSTACK(Event1tbl[% Sold], Event2tbl[% Sold]) )would bring across a couple of columns and move them down to align the days out.
OK, I understand. Assuming that you can add two columns to the event tables (Event number and Year), you can easily combine all events in one larger table. Automate it with Power Query or Copy/Paste manually if there aren't that many events. Whatever is easier for you.
Anyhow, you'll create a table like in the attached file which you can then analyze with a pivot table. Note that I just copied Event 1 three times into a new table numbering them 1, 2 and 3.
Riny_van_Eekelen that is fantastic, thank you!!
