Forum Discussion

Julia128's avatar
Julia128
Copper Contributor
Jan 12, 2024

Creating a waitlist

Hi,

 

I have a project where I would need to create a waiting list.

 

First page of worksheet I would like to have an "overview: of the data (with each column having its own set of data validation rules), Column A, B, C, D, E etc. In column E the subject of the heading would be "Suitable for waitlist" with Yes/No data options. 

 

Second page of worksheet "waitlist" I would like to have the waiting list involving Column A, B but only those that meet data validation rules in Column E  (which is Yes.)

 

I have tried some basic trials copying Column A in "overview" worksheet to "waitlist" by ="Overview'!A1 etc however it doesn't seem to bring the data validation boxes over.

 

Let me know any starting points! 

 

Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 12, 2024

    Julia128 

    To create a waitlist in Excel based on specified conditions, you can use a combination of formulas and filters. Here is a general guide on how you can set it up:

    Step 1: Overview Worksheet

    1. Overview Data:
      • Set up your main data on the "Overview" worksheet with all relevant columns, including "Suitable for waitlist" in Column E.
    2. Data Validation:
      • Apply your data validation rules in Column E to mark items as "Yes" or "No" based on your criteria.

    Step 2: Waitlist Worksheet

    1. Copy Relevant Data:
      • On the "Waitlist" worksheet, you can use a formula like this in cell A1 to copy the data from "Overview" only where "Suitable for waitlist" is "Yes":

    =IF('Overview'!$E1="Yes",'Overview'!A1,"")

      • Drag this formula across and down as needed.
    1. Remove Blanks:
      • You can apply a filter to remove the blank rows. Select your data, go to the "Data" tab, and click on "Filter." Then, uncheck the box for blanks in the "Suitable for waitlist" column.

    Step 3: Automation (Optional)

    If you want to automate the process more, you can consider using Excel Tables. Here's how:

    1. Convert to Table:
      • Highlight your data in the "Overview" worksheet and press Ctrl + T to create a table. Ensure that "Create Table" dialog box has the "My table has headers" option checked.
    2. Structured References:
      • Use structured references in your formulas, e.g., =IF(Table1[@[Suitable for waitlist]]="Yes", Table1[@[ColumnA]], ""). This will automatically adjust as you add new data.
    3. Power Query (Advanced):
      • You could use Power Query to filter the data based on your criteria and load it into your "Waitlist" worksheet. This would be a more advanced approach but can provide dynamic results.

    Remember, the exact solution may vary based on your specific requirements and data structure. Feel free to adjust the formulas and steps to fit your needs. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources