Trying to skip false data without Macro

[David Zissman]

I am working on a form for my managers that is essentially a dashboard pulling from 3 sheets in the workbook. I have a list of names in the dashboard where each person(last name is column A, first name in column B") is assigned a role (column D). I want to be able to pull data from the other sheets based on the assigned role. I want to be able to list all employees on the dashboard and then be able to have them load to the applicable worksheets based on their role.

Currently, I can load the names with no issue but when I try load the names by role and skip the roles that don't apply, I am running into issues. I can get the list to filter using advanced filters but I am not allowed to use a macro to auto-refresh the filter so that is not working. I am trying to use =IF(Dashboard!D2="PSS",OFFSET(Dashboard!D2,1,0),(IF(ISBLANK(Dashboard!A2),"",Dashboard!A2))) and it is filling in the role instead of the name so I feel like I am close. Any help would be appreciated.

 

[Chihiro]

Welcome to the forum David.

I'd recommend uploading sample set up that mirror your actual workbook with desensitized data.

If you can't, you can try something like below.

Set up dependent dropdown. Then pass that onto subsequent calculation etc.

https://www.contextures.com/xlDataVal02.html

Only thing that you can't do without VBA is to clear dependent selection after changing parent dropdown selection.

 

[GraH - Guido]

Only thing that you can't do without VBA is to clear dependent selection after changing parent dropdown selection.

One could have a conditional formatting rule in place that alerts the user about the mismatch of both drop down selections.

 

[Peter Bartholomew]

Only thing that you can't do without VBA is to clear dependent selection after changing parent dropdown selection.

Conditional formatting to give a red background would draw attention.
Also it may be possible to set a formula to use #N/A in place of the invalid string (i.e. one that doesn't match anything within the validation list).

 

[David Zissman]

Thank you for the tips. I am able to filter or call out the names. What I am not able to do is have the names fill to the other worksheets based on a filter. I want to have the master list on the dashboard and then be able to generate the list on the subsequent worksheets based on the value in column D. Then I would be able to pull the data from the additional worksheets back into the dashboard. My formulas are working properly for all other aspects except for loading the names to MA and PSS worksheets.

 

[Chihiro]

Oh, so you want it in reverse direction of what I originally interpreted as. Typically Dashboard is where segmented data is presented and master/data sheet holds all underlying data. Dropdown is used to control what is presented in the dashboard.

For your set up, would you be adding calculations and additional columns once the data is brought in from Dashboard to individual sheets?

If answer is no. Then I'd recommend having 1 sheet to display data, and dropdown to control what segment is displayed.

If yes. You'd use formula approach. Something like...

In A2 of MA Sheet.

=INDEX(Dashboard!A$2:A$23,AGGREGATE(15,6,ROW(Dashboard!$A$2:$A$23)/(Dashboard!$D$2:$D$23="MA")-1,ROWS($1:1)))

Copy across and down. If you want to display blank instead of #NUM! error. Then nest it in IFERROR(formula,"")

Note:
To make formula easier to read and to make data range dynamic. I'd recommend setting up dynamic named ranges.

 

[David Zissman]

If yes. You'd use formula approach. Something like...

In A2 of MA Sheet.

=INDEX(Dashboard!A$2:A$23,AGGREGATE(15,6,ROW(Dashboard!$A$2:$A$23)/(Dashboard!$D$2:$D$23="MA")-1,ROWS($1:1)))

Copy across and down. If you want to display blank instead of #NUM! error. Then nest it in IFERROR(formula,"")



Thank you! This it 99% of the way there. I need to add "BOTH" as an OR and when I attempted it I am getting an unexpected result. I need to fill MA or BOTH to the column.

 

[Chihiro]

Then change formula to something like below.

=INDEX(Dashboard!A$2:A$23,AGGREGATE(15,6,ROW(Dashboard!$A$2:$A$23)/(Dashboard!$D$2:$D$23={"MA","Both"})-1,ROWS($1:1)))

Note: Only change is "MA" to {"MA","Both"}

 

[David Zissman]

Then change formula to something like below.

=INDEX(Dashboard!A$2:A$23,AGGREGATE(15,6,ROW(Dashboard!$A$2:$A$23)/(Dashboard!$D$2:$D$23={"MA","Both"})-1,ROWS($1:1)))

Note: Only change is "MA" to {"MA","Both"}

Amazing! Thank you so very much!!!!!