Forum Discussion

kenan01210's avatar
kenan01210
Copper Contributor
Dec 24, 2023

Manifest & Seating Plan Formula Help

Hi all,   I am having trouble finding the correct formula for my attached problem, I need to find a formula that has a set of conditions for a seating plan, I want the values in column L6 to change...
Formulas and Functions
rachel's avatar
rachel
Iron Contributor
Dec 24, 2023

kenan01210 

 

Hi,

 

If you have office365, I think you can use XLOOKUP to do this. (I made up some random names in the attached example).

 

kenan01210's avatar
kenan01210
Copper Contributor
Dec 25, 2023

rachel 

 

Thank you so much for your help, I managed with what you have supplied me the exact formulas i needed and even learnt how to write them up myself, thank you so much!

 

I just got one problem, in column M & N of the below sheet, I have made it so that as I allocated seats in column J it highlights the seat e.g. 1A and changes colour to green to indicate the seat has been taken. The only problem is the formula comes up as #N/A when I only want the column to change colour once the seat has been assigned but I have tried xlookup and vlookup and can't seem to figure it out. For instance 6A us #n/a but i want the seat number to remain and just the column change colour. 

 

Could you please assist in helping with this issue just one more time, thank you. 

 

https://1drv.ms/x/s!AvIg6ufctWyqgUhiayK7Jaf3ZIDj?e=0xbPwN&nav=MTVfe0QxNjVFQjAxLThENzktNEU5MC1BOUFFLTA0QjJCNEJEQzlCM30

 

  • rachel's avatar
    rachel
    Iron Contributor
    Dec 25, 2023

    kenan01210 

     

    Glad to hear that you find XLOOKUP formulas straightforward!

     

    I updated the formula in column M, IFERROR replaces "#N/A" with blank.

     

    I also updated the conditional formatting. if cell in column M is not blank (i.e seat is taken), then the cell turns green:

     

Resources