Create a dependant drop down list based on multiple entries with the same name

Hi all,

I have a spreadsheet (see attached) that has multiple formulas used.

At the moment there is 2 sheets:

- Guest Stays

- Current Approvals

I populate 'Guest Stays' each time a guest stays at the property. Currently I have a drop down list for the name that draws from the 'Current Approvals' Sheet and automatically populates approval number, expiry date etc.


On the Current Approvals sheet, any one person can have multiple approvals with different expiry dates and approval numbers at any one time.

What I would like to do is:

Using the 'Guest Stays' sheet have a drop down list for the Guest Name (column B) that then makes the Approval Expiry (column C) turn in to another drop down list showing all approval expiry numbers related to that specific person from the 'Current Approvals' sheet so that you can choose which one you would like to use for that stay.

Is this possible? I appreciate your help so much!

Thanks

Heidz

Maybe I'm missing something but isn't this just a case of filtering the Approvals sheet for the name you want? I can't see why the Guest Stays sheet needs to be involved.

Otherwise please show a before and after situation, using a new sheet to show the results you and explaining what actions you've taken to arrive at the results.

On the sheet I have provided, the approval expiry dates are all different on the Current Approvals.

1. The actual spreadsheet I am using contains personal data so I cannot share it due to that. The sheet i have provided it loosely based on it with the same kind of information. It is exactly the same with less people

2. The Approval Expiry in 'Guest Stays' is not carrying over properly from 'Current Approvals' when choosing a guest. As you can see in 'Current Approvals' all expiry dates are different for the same person.

It is not a case of filtering, I feel like I explained what I was trying to do pretty well. Just wanted to know if it is possible and also since checking the spreadsheet why when I choose a name the expiry dates does not bring over correctly either.

What I would like to do as I said in my initial post is create a dependant drop down in the 'Guest Stays' sheet starting with:
- Guest Name (drop down list of names in 'Current Approvals'
- Approval Expiry (drop down list of all the expiry dates attached to that persons name in 'Current Approvals)

If I understand perhaps this will help.
On the Current Approvals sheet distinct list of names (column K) is populated using:

Formula:

=IFERROR(IF(MATCH(0,INDEX(COUNTIF(K$1:K1,A$2:A$16),,),)>COUNTA(A$2:A$16),"",INDEX(A$2:A$16,MATCH(0,INDEX(COUNTIF(K$1:K1,A$2:A$16),,),))),"")

Data validation for the Guest Name column on the Guest Stays sheet uses the source: =OFFSET('Current Approvals'!$K$1,1,0,SUMPRODUCT(--('Current Approvals'!$K$2:$K$16<>"")),1)
A new named range, Expiry_Date_2 has the Refers To as:

Formula:

=OFFSET(Table2[[#Headers],[Expiry Date]],MATCH('Guest Stays'!A3,Table2[Participant Name],0),0,COUNTIF(Table2[Participant Name],'Guest Stays'!A3))

The data validation for the Approval Expiry column uses the source: =Expiry_Date_2
Let us know if you have any questions.

That is because the VLOOKUP is only finding the first approval number assigned to the corresponding name in column B.
Replace the formula in C3 (Guest Stays sheet) with:

Formula:

=IF(ISBLANK(B3),"",INDEX(RP_Approval,AGGREGATE(15,6,(ROW(RP_Approval)-ROW(Table2[[#Headers],[RP/Approval '#]]))/(Table2[Participant Name]=B3)/(Expiry_Date=C3),1)))

Let us know if you have any questions.

Hi JeteMc,

I am just testing out the sheet, if I add a new approval to the current approvals sheet - will this all automatically to the defined name list.

It seems when I inset a row and add a new approval within the sheet it creates a space in defined name list. Not sure how too get around this

Thanks again

Lets try replacing the formula in cell K2 and down (Current Approvals sheet) with:

Formula:

=IFERROR(IF(MATCH(0,INDEX(COUNTIF(K$1:K1,Table2[Participant Name]),,),)>COUNTA(Table2[Participant Name]),"",INDEX(Table2[Participant Name],MATCH(0,INDEX(COUNTIF(K$1:K1,Table2[Participant Name]),,),))),"")

If there is a name in the Participant Name cell of the new row then the formula should work. If not, please upload a sample demonstrating the issue.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

For what reason do you wish to remove the attachment? You should not be sharing anything that should not be shared on a public forum, and we expect sample workbooks to stay in place as a part of the thread. You will need a very good reason for us to agree to removing it.