Main Menu Macro Navigation Tabs Menu Admin Bands

Main Menu Macro Navigation Tabs – Menu / Admin / Bands / Venue / Lighting Technicians / Sound Technicians / Bookings / Expenditure

Bands Area defined as Bands from A 2: H 100

Venues Area defined as Venues from A 2: I 100

Lighting technicians Area defined as Light from A 4: C 100

Sound technicians Area defined as Sound from A 4: C 100

Booking Details Data Validated to a list from Sound Data Validated to a list from Bands Data Validated to a list from Venues Data Validated to a list from Light Data validation has to be set to INDIRECT to avoid problems with inserting new data 6

Expenditure

Expenditure Heading How is it worked out Date Referenced from Booking Band Referenced from Booking Venue Referenced from Booking Band Cost =VLOOKUP(Bookings!B 3, Bands, 8, FALSE) Venue Cost =VLOOKUP(C 3, Venues, 9, FALSE) Light Cost =VLOOKUP(Bookings!D 3, Light, 3, FALSE) Sound Cost =VLOOKUP(Bookings!F 3, Sound, 3, FALSE) Total Cost =SUM(D 3: G 3) Amount of Tickets =VLOOKUP(C 3, Venues, 8, FALSE) Total Income =J 3*Bookings!H 3 Profit Loss =K 3 -H 3

Expenditure Conditional Formatting – colours – Green, red and blue.

Extension - Programmes Area defined as Programmes from A 3: C 100

Extension - Programmes Inserted next to Total Cost Formula for Programme Cost – Assuming half the people will buy a programme =VLOOKUP(Bookings!H 3, Programmes, 2, FALSE) *

Extension - Programmes Total cost adjusted - =SUM(D 3: H 3)

Extension - Advertising 2 new columns Advertising charge - Manual entry Advertising revenue - =L 3*VLOOKUP(Bookings!H 3, Programmes, 3, FALSE)

Extension - Advertising Total Income Adjusted - =(K 3*Bookings!I 3)+M 3