filter multiple sheets using data validation

0
votes

I have a workbook with four sheets and want to make a dashboard. In the dashboard I have 4 data validation drop down lists (Part, Date, Shift, Part Number). I want when someone selects the value from the drop list to look for the data that relate to the selections. I currently have =FILTER('sheet1'!A5:FP,('sheet1'!C5:C=Dash!A6)*('sheet1'!A5:A=Dash!A4)*('sheet1'!H5:H=Dash!A8))"Sheet 1" is the name of the sheet I want the data pulled from, however because the first parameter of =FILTER is range it wont allow me to reference my data validation dropdown found in A2. Is there a way to have the filter function look at what is in A2 and give me the data from the selected sheet, and how? Thank You in advance! enter image description here

1
google-sheets

1 Answers

2
votes

I think you are looking at the indirect function in google sheets, please try:

=FILTER(
  Indirect(A2&"!A1:FP"),
  (Indirect(A2&"!C5:C")=Dash!A6)*(Indirect(A2&"!A5:A")=Dash!A4)* 
(Indirect(A2&"!H5:H")=Dash!A8)
)