Q: Excel formulas and conditional lookups based on multiple criteria and sheets I have 2 sheets:


*

*sheet_a is a styled print-ready layout for a single data record

*sheet_b is a bulk data table which is continually growing. Each row corresponds to a single complete record


Currently I am using a VLOOKUP to collect the data from sheet_b and put it in the respective cells in sheet_a. I have a drop down list on sheet_a which allows me to select a single record at a time to view.
Now I want to introduce a second drop down list to sheet_a where I want to select 1 of 4 specific conditions relating to the value of a cell in a specific column of each record on sheet_b.
I only want the entries that meet this condition in sheet_b to be made available in the range of records I can view on sheet_a.
Can anyone help?

A: As I understand it, you are looking for a way to filter the list that is used in the drop-down on Sheet_A.


*

*add a column to the source data and calculate or mark manually which of the four conditions the record belongs to.

*on Sheet_a add a drop-down field where the user can select from the four conditions. Name this cell "criterion"

*in the source data table, add a column with a formula that returns the row number if the current row matches the selected criterion. Something like this copied down
=IF(B2=criterion,ROW(),"")

*create another helper column that contains only the items that match the criterion using a formula like this and copied down
=INDEX(Data,SMALL($E$2:$E$18,ROW(A1)))

*use a dynamic range name called "FilteredList" that contains only the values of the result list, not the errors. The formula for "FilteredList" is 
=Sheet1!$F$2:INDEX(Sheet1!$F:$F,MATCH("zzzzz",Sheet1!$F:$F,1))

*change the drop-down that is currently used to select a record to source its values from =FilteredList

