Excel for Ticketing in Concert?

[ahmike]

Dear Excel Professionals,

I work in an art school that organize concerts often.
In the past we manually match student name with seat numbers in concerts.
It is inaccurate and time consuming as we have to process hundreds of orders each time, and students make changes for the orders... say switching from concert no. 1 to concert no. 3.

I have an idea to shift the burden to excel formulas... Here it is:
***
Question no.1

Sheet 1: Ticket Orders
Column A: Student Name
Column B: Class
Column C: Starting seating number
Column D: Ending seating number
Column E: Starting seating number
Column F: Ending seating number

e.g. Harry Porter of Class 3A orders 4 tickets for concert no.1 and 9 tickets in concert no.4

Cell A2: Harry Porter
B2: Class 3A
C2: 1 (meaning he want to be in concert no.1)
D2: P9 (meaning I assigned seat P9-??? to him)
E2: P12 (meaning I assigned seat ???-P12 to him)
F2: 4 (meaning he also order tickets in concert no. 4)
G2: Q10 (meaning I assigned seat Q10 to???)
H2: Q18 (meaning I assigned seat ???to Q18)

****

Sheet 2 = seating plan of concert 1
Sheet 3 = seating plan of concert 2
Sheet 4 = seating plan of concert 3
Sheet 5 = seating plan of concert 4

I want the text "Harry Porter (Class 3A)" to appear in cells P9-P12 in Sheet 2, as well as cells Q10-Q18 in Sheet 5. I know it won't be easy... any possible ways to do so? I guess vlookup may be helpful?


Question no.2
Alternatively, I am thinking of making drop box in the seating plan from sheet 2 to 5, so that when I select names from the drop box, the corresponind seat number and concert number automatically appear next to the name of the student in Sheet 1. Again it won't be easy... Any simple way to do it?

I sincerely hope that I may find some hints here... I don't wish to see my colleagues suffering from matching unmatched data...

Graceful if someone helps.

Mike