Creating a dynamic array to display multiple specific entries based on criteria

Try this formula - I've made a few minor changes to make it work.

It's an array formula so you'll need to do CTRL SHIFT ENTER to enter the formula.

Formula:

Please Login or Register  to view this content.

That seems to have done the trick! Thanks!

I'll let you know if I need anything else

Hello again.

I have another question. I'm trying to make the formula dependent on multiple criteria. For example, if a customer is signed up for tomorrow's lunch they they need to have their trip as tomorrow's date and they have to select "Lunch". Or if they are having dinner tomorrow they need to have tomorrows date and select "Dinner".

Formula:

Please Login or Register  to view this content.

I've attached another sheet so that you can see what I mean.

Thanks for the help

Apologies for the inappropriate thread title. It has been fixed now

To reiterate the issue:

I am trying to create a a dynamic array to display multiple specific entries based on criteria. The criteria, for example, could be that a customer is going on a trip tomorrow, and will be eating lunch. I want the customer's dietary restrictions to appear on tomorrow's lunch list. For example:

On the 'CUSTOMERS' tab we can enter in column A the customers trip date. In Column G we enter their dietary restrictions, In Column K we enter if there will be present for lunch, dinner, both etc. On a separate tab - 'Trip Info' - I want their names and their dietary info to be displayed in columns B & C if the appropriate info has been entered on the 'CUSTOMERS' Tab

Here is the formula I've been trying:

Formula:

Please Login or Register  to view this content.

I've attached a sheet to help explain

Thanks in advance

Hello JetMc,

Thanks for the solution. It's close to what I am looking for, but not quite there. The formula you wrote has a very limited rage (row 2 - Row 6), where as I am looking for a dynamic formula that can go to infinity (we don't know how many customers we will get). I actually managed to adapt it into something that almost works.

I tried to post the fomula I made here but excelform keeps telling me "Access Denied - Sucuri Website Firewall -Your request was not authorized due to its content (HTML code not allowed)" I have formula tags around the formula, but it still won't let me post it properly. To get the forum to let me post, I had to put spaces in the middle of the formula

Formula:

Please Login or Register  to view this content.

This formula goes to infinity and works perfectly for my purposes. The only issue is that want to make it somewhat of a wildcard formula (See below)

Formula:

Please Login or Register  to view this content.

(The above formula does not work, all I did was add an asterix)

The reason I want to make it a wildcard formula is because I want customers to show up on the lunch list if CUSTOMERS!K:K says "Boat Lunch" or "Boat Lunch + Boat Dinner".

I've attached an example, and in the example, a customer named "Lizzie" should show up on both the Lunch and the Dinner lists, but she doesn't show up on either. Also, with the wildcard formula, the last customer appears as "NUM".

Is there a better way to go about making this formula?

Thanks again.

Hey JetMC,

Thanks! That formula worked perfectly!

I agree that entire column references are not the best because they slow things down a lot. The reason I use them is because if cells get deleted then the reference row can shrink. I solved that problem by editing the formula in the following way:

Formula:

Please Login or Register  to view this content.

Thanks for the advice on scurri as well!

I have one more questions. In my opinion this is the most difficult part of the sheet, I've been working on it for several hours but I don't even know how to approach the problem.

Basically, in my country, in order to purchase harbor tickets for our customers we must present a specific form that lists the customers name, country, birth year, and if they are male of female. To simplify things the form is laid out as follows:

Capture.PNG

The formula for name name and country are easy. It is the one I have already been using. The formula for birth year /Gender is more difficult because the birth year should only show up if a specific gender is entered, and the cell should remain blank if the other gender is entered. I tried adapting one of your formulas:

Formula:

Please Login or Register  to view this content.

But it doesn't actually work. Even if the above formula did work, I would still be concerned that if 2 customers had the same name, the info would not show up properly as it would display the first customer's info twice.

To further complicate the formula, Only customers who are going out in the morning (AM) should appear (there will be a separate "PM" Sheet). This means we have to enter "AM" into Column J on the Registry.

Again, I've attached a sheet for better reference. You are looking at the AM Boat Reg tab.

Any suggestions for how to fix this issue?

Thanks,

Hey JetMC,

That's a really smart solution and helps a lot! I've played with it a bit and there is one specific situation where it does not work. If we have two customers with the same name on different days, or (more likely) the same customer over 2 different days, their information will not show up on the second day because the formula on the AM Boat reg tab shows the ID for the first entry instead of the second entry.

I've attached a sheet to show you what I mean. This sheet contains a very real entry that we put in almost every single day. If you go to the ÁM Boat Reg' tab you will see that the birth year doesn't appear.

Please let me know your thoughts

Actually, Ignore my last post. I don't know why it wasn't working, but is now.

Thanks for this solution I'd give you rep but ti won't let me until I give some to someone else.