formula to return cells in a row based on 2 conditions

[melewie]

Hi all,

Its been a while but I find myself completely stuck once again, any help is greatly appreciated.

I have a workbook with a sheet Data_sheet with a report on that's updated regularly. I have been asked to create a dashboard (and accepted before I realised VBA is not an option)

What I am trying to achieve is on the dashboard page if a user selects 1,2,3,or 4 from a drop down on the dashboard sheet (column D on data sheet) then all of the job that are priority 1 (or what ever the number) and are not complete (column J on the data sheet) will pull through to the dashboard sheet.

I have tried using VLOOKUP but this will only return the first data that matches the criteria so can only get one to pull record to pull across.

I hope this makes some kind of sense, If I can help any further please ask away

thanks in advance

 

[arthurbr]

Depending on your XL version ( your profile does not say which?), perhaps the FILTER function might help?

 

[melewie]

Thanks for the reply I did not know this existed!

I am still stuck but feel like I'm finally going in the right direction

=FILTER(Data_Sheet!B:K,(Data_Sheet!D:D=Dashboard!B11)*(Data_Sheet!J:J="<>*complete*"),"")

this is returning "" To my 2 brain cells are struggling to figure this one out

 

[melewie]

I have figured it out seems Filter works beeter with named table and ranges...... Maybe?!

=CHOOSECOLS(FILTER(Table1,(Table1[Job Priority]=Dashboard!B10)*(Table1[Complete check]=Dashboard!A1),""),6,7,8,9,10,11,2,3)

Thanks for pointing me in the right direction Arthur!!!

 

[arthurbr]

You're welcome. Please add your XL version in your profile. It might be handy in the future