Hello

My question is can I achieve what i want to using Excel or would I have to use Access.

I follow a music band who have toured for many years and I would like to record and analyse every venue they have played at, the dates, the amount of seats sold, costs, profit etc.

So, over the last 10 years, we may have played in one particular venue, 6 times.

I want to be able to find out how many times we have been there, the dates, the sales records etc and the cumulative number of seats sold at that venue over the period.



Many thanks, Rob

Access will clearly be an overkill

For 10 years, when the band played every single day, there will be less than 3700 rows of data (a single perfomance a row in table). Excel can handle this and more easily, and you'll have less headaches.

Leave at top of your table some empty rows where you'll calculate statistics.

It'll be reasonable declare your data as table, so enter headers into header row (at least row 3), and try to avoid spaces/underscores. P.e.
PerformDate, Avenue, Seats, Sales, ...
(Keep all data for single performance in single row)

Select headers and couple of underlying rows, and define the selection as Table (from menu: Insert>Table, check 'My table has headers')

At top of the sheet (leave an empty row above the table), aligned with table headers, enter formulas for calculating your statistic - use SUBTOTAL() function to calculate min, max, count, sum, average etc. You can have them in single row, when for one parameter only one stat is needed, but it will be better to have a separate row for every stat type you'll use - and leave cell empty when given stat isn't needed for particular parameter.

Use Freeze Panes to keep table headers and statistics always visible.

Enter your data into table.

Now, whenever you set any autofilter combination on your table, the statisics above will calculate min, max, count, sum, average etc. for filtered data

Many thanks for your reply and my apologies for not responding sooner but I had to go away for two weeks at very short notice.

My Excel table has the following 12 columns and in the last few years the band have performed 816 gigs.

Day - Date - Venue - City - Country - Capacity - Sold - Fee - % - Gross - Net - Note


I regularly need to find when we last played at a place and how it performed sales wise, or how many times have we played a particular venue and has it been profitable etc.

Is Excel the right tool and if so, I should be learning more about it. I have never used SUBTOTAL VLOOKUP or INDEX/MATCH functions so it is all a steep learning curve.

Could you please post a small sample sheet with some data and expected results?

OTOH would a Pivot Table help?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Seems like a good pivot table application.

Here is a sample

ID Day Date Country City Venue Capacity Sold Fee % Gross Net Note
1 Thurs 19/10/2016 Norway Oslo Oslo Concert Hall 1200
2 Fri 20/10/2016 Sweden Malmo Malmo Live 2300
3 Sat 21/10/2016 Sweden Stockholm Cirkus* 2200
4 Sun 22/10/2016 Germany Bonn Beethovenhalle 5000
5 Fri 29/10/2016 England Buxton Opera House 975
6 Sat 30/10/2016 England Salford The Lowry 2200



I have been playing with pivot tables and have managed to come up with some fairly useful ones. What I can't work out at the moment, is how to show the last date we visited a certain venue. I have been trying to filter this info but not much luck at the moment.

Thanks, Rob

How about:

LOOKUP(2,1/($F$2:$F$8=$N$2),$C$2:$C$8)

withe venue to look up in N2 venues in F2:F8 and dates in C2:C8.

It's preferable to post an actual Excel sheet. You can do this by clicking Go Advanced.

At any rate, I have attached one that determines last date played for each venue using a pivot table. I added another date for Malmo to illustrate it. To do this, just get Max of Date.

Lots of ways to skin this cat though.

A few things:

* I had to change the date formats above to the silly American m/d/yyyy so don't let that confuse you.
* The day of the week data are somewhat superfluous as that can be shown in the Date field. Just choose the appropriate formatting.

Attached Files

Gigs.xlsx (14.0 KB, 14 views)

Sorry, I am new to this depth with Excel. How do I go Advanced?

Thanks gebobs, that is exactly one of the things I am looking for. I have been playing around with it using filters etc.

We have played in over 260 different venues all with different names. However some of these are in different cities and, indeed, different countries. so, New Theatre, could be in Oxford, Blackpool or Sydney, Australia.

What I can't quite work out is how to get a report with three columns, venue, city, date (Last Played)

I have filtered out the countries I don't want, but I can't get past the City in a row above the venue and then a city total beneath that.

I have actually got rid of the subtotals but I want a 3 column answer rather than 2 rows. Is there a way to do this?

By "Go Advanced", I am referring to the button at the bottom of this page next to Post Reply. Using it, you can attach a spreadsheet.

To add more details like city and country, all you need to do is add those fields to the pivot. See attached.

If you attach a sample of you sheet, I'll help you out as much as you want.

Attached Files

Gigs.xlsx (16.0 KB, 9 views)

Thanks gebobs

I was replying from within the post and could not see the Advanced button. I'm new to forums too!

What you have sent is exactly where I have go to.

I have filtered out the country to get a list of the UK dates only but would like to get the result in the attachment.

Attached Files

GigsList.xlsx (8.5 KB, 13 views)

Do you still need help or do you have what you want?

Sorry gebobs, I answered your earlier reply and posed another question. There is an attached file with that reply showing the result I am trying to obtain, ie City, Venue and last Date on the one row instead of over two rows.

Struggled with this for the last hour trying all sorts of variations without success.. Not sure if it is possible.

Thanks

Rob

I posted a reply which was meant to be here, but accidently posted it to Access forum to same topic you have there.

As far as I know, you can't get pivot tables to display like like that. You could filter the table but that's a pain in the arse.

Access can do it, fer sure. So long as you don't need to collaborate with anyone, then that would be the easiest way. Overkill, as Arvi said earlier.