Populating a list based on two variables, excluding entries which are blank

[Perth Excel]

Hello all,

I have a problem which a few hours of googling and playing with formulas has not seemed to have turned up any results.
Although the details are in the attached spreadsheet I shall explain anyway.

I am attempting to populate a list (on the first tab) based on two variables.
One being the type - Expressed by columns of data in the second tab with a data-validated drop down menu on the first tab.
The other being that the word "Yes" appears in the row matching the type.
With all values not meeting these criteria (ie. blank spaces) excluded from the populated list.

A simple example would be as follows.

Name..........Standard..........Standard +..........Art

The A...............Yes................Yes......................
Big B.....................................Yes.................Yes
Mr C.................Yes......................................Yes

When "Standard" is selected from the dropdown menu on the first tab, the list populates as so...
The A
Mr C

When "Standard +" is selected from the dropdown menu on the first tab, the list populates as so...
The A
Big B

When "Art" is selected from the dropdown menu on the first tab, the list populates as so...
Big B
Mr C

If at all possible I would like to avoid VBA and work it out with formulas.


Here is the example sheet.
NCL Example.xlsx


If there is anything that needs clarification then please let me know.

Thank you in advance,


Perth Excel

[ChemistB]

In Application Data M4 dragged down (you can hide this column later)

=IF(INDEX($D4:$L4,MATCH(Main!$G$7, 'Application Data'!$D$3:$L$3,0))="Yes",COUNT($M$3:M3)+1,"")

In Main B11, dragged down

=IF(ROW(A1)>COUNT('Application Data'!$M$4:$M$29),"",INDEX('Application Data'!$B$4:$B$29,MATCH(ROW(A1),'Application Data'!$M$4:$M$29,0)))
See attached. Does that help?

[tigeravatar]

Perth Excel,

Attached is a modified version of your posted workbook. I made several changes to layout to make it more Excel-friendly. You'll notice there are no skipped columns in the 'Application Data' sheet. Row 1 is used for headers, and data starts in column A. I made very minor changes to the layout of sheet1. Mostly I just removed the merged cells portions because they are unnecessary.

With the layout now in a more Excel=-friendly format, I created three dynamic named ranges. The first one is for the list of application names. The second one is for the types of installation. The third one is for the table of resulting data in the sheet 'Application Data'.

For the list of application names, named list_Names:

='Application Data'!$A$2:INDEX('Application Data'!$A:$A,MAX(2,ROWS('Application Data'!$A:$A)-COUNTBLANK('Application Data'!$A:$A)))

For the types of installation, named list_Types:

='Application Data'!$B$1:INDEX('Application Data'!$1:$1,,MAX(2,COLUMNS('Application Data'!$1:$1)-COUNTBLANK('Application Data'!$1:$1)))

For the resulting table of data, named tbl_Data:

=OFFSET('Application Data'!$B$2,,,ROWS(list_Names),COLUMNS(list_Types))

Then, in the Main worksheet, in cell C4 is a data validation drop down using the named range list_Types.
In cell C6 and copied down is this formula:

=IFERROR(INDEX(list_Names,MATCH(1,INDEX((COUNTIF(C$5:C5,list_Names)=0)*(INDEX(tbl_Data,,MATCH($C$4,list_Types,0))="Yes"),),0)),"")

Now when you select a different item from the Type of Installation drop-down, the Application List will show the items for that type. You can add to the table in the sheet 'Application Data' and the additional information will be automatically picked up by the dynamic named ranges.

[rvasquez]

Hello there,

Attached is a macro version. I prefer to use macros as they are able to function correctly even when your lists grow and categories are added.

[tigeravatar]

rvasquez, dynamic named ranges allow for that same functionality also.

[rvasquez]

Thank tigeravatar, I had tried your workbook and I guess I just added it below the last Category instead of inserting it and it didn't work. But it does if you insert it.

Thanks again! Good to know!

[Perth Excel]

@ ChemistB - Thanks for the quick response this is just what I was looking for!
@ tigeravatar - I had tried named ranges (and had them as named tables as well with the ranges pointing to these as to make them dynamic named lists) but i just couldn't figure out the forumla. The spaces were helper columns I was using from a different project but I realised I couldn't use them in this one after i tried it. As for the merged cells in the first tab, That was merely for User Friendliness (and because I planned on adding more features later based on this methodology).
@ rvasquez - I was trying to avoid VBA / Macro's as I don't have much experience with them, and I find working out what is going on in formulas easier if I come against a problem but your solves the problem as well. I eventually want to learn how to programm VBA / Macros, any idea's on quick learning resources?

Thanks everyone for your hard work (or easy if you found it simple). + Reputations all round and SOLVED (3 different ways).

[Cutter]

@ Perth Excel

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

[Perth Excel]

@Cutter - Apologies, I had tried to unsuccessfully many times but under the thread thools where I usually have "Mark as Solved" it read "Mark as Unsolved". However many times I refreshed the page, deleted browser cookies etc. I could not get it to change. Not sure if this is possibly a bug or my computer having a bad day. Nevertheless I have managed to do this now (after another three rounds of the same "Unsolved" instead problem as yesterday).