S/Sheet improvement

[bobbybee]

I am the ticket secretary for a local theatre goup and have devised an excel spreadsheet to make the booking and ticketing that much easier.

My spreadsheet has a daily seating plan which will record the type and price of each 'taken' seat. It also shows the totals of each type of ticket, how much ticket money has been received, how much is due and the percentage of seats sold. All this information feeds into a 'stats' sheet which will give relative info or the production run.

It is necessary to record customer details, and to this end I have created another tab upon which I manually enter the relevant customer detail along with ticket numbers and payment details etc.

I think my system, although helpful, can be improved by reversing the process that I currently use.

I think I should create 'a customer card' which, along with the usual essential info, would have fields to enter the performance day/date, the type of ticket (Full, Concession etc), the price of the ticket and the allocated seat numbers.
The process would then require those ticketing details to be transferred to relevant seat on my current seating plan. The present system would then take over to produce the 'Stats' info.

I'm thinking that there should be a drop down list of available seats for any given performance which would then reflect any 'taken' ticket when the list is used again

Long winded set up (sorry) but this is where help and advise is needed:
What formula would link a ticket type for a particular performance to the relevant established seating plan?
How do I create a dropdown list and how would I reflect 'availabilty only'?

Is this idea at all possible?

I have attached my Seating spreadsheet to show my work thus far.

Bobbybee

[Richard Buttrey]

Hi,

I think you're going to need a macro to analyse the array of seats for each performance and output a list of available seats. The current layout doesn't easily lend itself to an Advanced Filter which would have been the first choice.

I couldn't see a tab which contained customer details such as ticket numbers, where is that?

Regards

[bobbybee]

Hi Richard,

Thank you for your response.

I removed the customer detail tab as it was only designed as a list of customers and it was this part of the process which I wanted to improve.

Re ticket prices.
Cell W10 on each performance tab shows the full price as £10 (my code for this is A)
Z10 shows the block booking price as £9.50 (My code for this is B)
AC4 shows the complimentary price as £8.50 (My code for this is C)
AF10 shows the block booking complimentery as £8 My code for this is D)

The ABCD codes above refer to a ticket 'taken' (no monies received)

The green boxes show seats that are paid for (AP, BP, CP, DP)

Hope that helps

Bob

[bobbybee]

Sorry, Roy.

New to forum, link to re located post not made.

[Richard Buttrey]

Hi,

Yes.. I had deduced that. I thought you were asking how you could identify in a list the unsold seats at any point in time. Was I mistaken on that point?

Rgds

[bobbybee]

Richard,

That is certainly part of the scenario I wish to create

My current S/S lets me know how many seats have been taken and also how many are available without ever identifying the seat numbers of either.

At present I can just look at the seating plan to see that, for example, seats F3 & F4 are available. So, provided they are acceptable to the customer, I can allocate them by a series of codes depending on the type of seat (full, comp etc).

I want to refine this process by creating and then initially accessing a 'customer card' and see, by means of a drop down box, the available seats. Those seats that are acceptable to the customer can then be 'clicked' and will appear in (for example) a 'Tickets Allocated' field.
The next step would be to allocate the type of ticket which of course attracts differing prices. Again, a drop down menu listing the four catergories of tickets currently used may be suitable. When 'clicked', the choice code (one of eight currently in use), will be tranferred to the actual seat field on the current seating plan whilst a record is made of the actual ticket price(s) on the customer card.
Tickets, once allocated, are then removed from the availability list.

Thats my plan, Richard, but quite how to go about it I know not!!

Any advice or suggestions would be gratefully accepted.

Regards,
Bob