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:
=IFERROR(INDEX(Registry!$B$2:INDEX(Registry!B:B,6000),SMALL(IF(Registry!$A$2:INDEX(Registry!A:A,6000)=TODAY()+1,IF(ISNUMBER(SEARCH(B$1,Registry!$K$2:INDEX(Registry!K:K,6000))),ROW(Registry!$A$2:INDEX(Registry!A:A,6000))-1),""),ROW(1:1))),"")
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:
=IF(Registry!D$2:INDEX(Registry!D:D,6000)="Female","",IFERROR(INDEX(Registry!E$2:INDEX(Registry!E:E,6000),MATCH(B18,Registry!$B$2:INDEX(Registry!B:B,6000),0)),""))
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,
Bookmarks