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