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 validatio...
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