Data validation Drop down and If statement

(NOT if one says this give me another set of options)

Hi

I working on a spreadsheet for my BTEC IT , my task is To create a spreadsheet to manage a football clubs 'Season ticket Information'. one of my requirements is have a system to automatically assign people if they have no choice on seating area to the area with most space.

I have two columns Requested and allocated seat ( L and M ) Both with data validation of my seating areas (Found Below). I have already made the a table to workout the area with the most free space found on my FX sheet which automatically updates. I would like to able to select "Not Specified" in column L (Requested) and in column M (Allocated) it two input the area which the most free space found from my ranked table and overwrite any thing that has already been selected in column M

Is this possible and if so how would i do this as cant find anything online and my teacher is useless, would be much appreciated :p

Seating Areas:

• North Upper
• North Lower
• East Upper
• East Lower
• South Upper
• South Lower
• West Upper
• West Lower
• Home Goal
• Away Goal

Thanks
Mathew Speed

My spreadsheet if you want to have a look i have attached it as a zip. Mathew Speed IT CA.zip

Can you post a sample workbook to the forum that will have enough detail to get the point across?
I dont want to download a 40MB file lol

Can you make a SAMPLE workbook, and not that monstrosity LoL
We would need sample data, and then a proposed solution or what you are looking for exactly. You can mock up the solution without formulas and such.

I think that you need VBA to do this. The best that I could find was to use a formula like this for the data validation list in C3.

Formula:

=IF(B3="N/A",E3,$H$2:$H$11)

Unfortunately, all this does is to limit the choice in C3 to the value in E3 as you can't "push" a value into a cell with workbook formulae. You can only "call" a value by a formula into a cell.