Good evening excel expert,
what i'm trying to do is only to filter month wise when i select d3,h3 and l3 column, im using below formula but result not getting, request you to please help in this regard.Dashboard.xlsx enclosed the file for your reference.
FILTER(CHOOSE({1},Data!C2:C393,Data!K2:K393),Data!K2:K393=Master!H3)
Is this what you want?
=LET(f,FILTER(Data!A2:M393,(MONTH(Data!C2:C393)=MONTH(DATEVALUE(Master!Criteria&" 1")))),CHOOSE({1,2,3,4,5},INDEX(f,,3),"",INDEX(f,,2),"",INDEX(f,,12)))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Is the year important... or will you only ever have one year of data in your sample?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you for your quick reply, you've mistaken what i want to try is if i select from drop down d3 and L3 of h3 and b3 result should be only india mart of imple PO received
Its a helper column for countifs sir what i want is month wise to filter lets say if i select india mart, month may, customer impel, and PO received, result should beOriginally Posted by Glenn Kennedy
column1 column2 column3
02-May-23 Sai Controls PO Received
That's not what your attempted formula was suggesting and you failed to mock up expected results!
Try adding to the filter part:
=LET(f,FILTER(Data!A2:L393,(MONTH(Data!C2:C393)=MONTH(DATEVALUE(Master!Criteria&" 1")))*(Data!L2:L393=Master!L3)*(Data!B2:B393=Master!B3)*(Data!K2:K393=Master!H3)),CHOOSE({1,2,3,4,5},INDEX(f,,3),"",INDEX(f,,11),"",INDEX(f,,12)))
Last edited by AliGW; 05-29-2023 at 08:09 AM. Reason: Workbook added.
You did not answer my Q. In your real sheet can data from May 22 and May 23 BOTH be present? Do you also need to search for ther YEAR?
Sorry for replying you late as I was on the way yes sir both
I have added in a cell to select the year (you can turn it into a dropdown yourself).
This formula incorporates the year:
Formula:
Please Login or Register to view this content.
Is HSTACK available in Excel 2021, Glenn? I avoided it because I think it's only in 365.
Oh POOH! You're right, Ali.
So...
Formula:Please Login or Register to view this content.
Thanks mam its work awesome
Thank you sir, its work perfectly but instead of dealer name i want company name in column L8
Mine gives the same answer as Alis... IMPEL... and the header says Dealer Name, so I assumed you wanted the Dealer Name!!!
Since they give the SAME answer, how can hers be correct and mine incorrect??
Which column from the raw data do you want returned in L9... under the header "Dealer Name".
Please explain specifically which raw data column you want to have returned!!!!
Respected sir,
I've no doubt in your formula my only desire is to filter company name in column L9
Last edited by AliGW; 05-30-2023 at 10:35 AM. Reason: Please do NOT quote unnecessarily!
Once again, you did not directly answer my Q. If it is column B that you want in L9, change the formula to:
=LET(F,Data!A2:L393,A,FILTER(F,(EOMONTH(+INDEX(F,,3),-1)+1)=DATE(P3,MONTH(D3&1),1)*(INDEX(F,,2)=B3)*(INDEX(F,,11)=H3)*(INDEX(F,,12)=L3)),CHOOSE(SEQUENCE(,5),INDEX(A,,3),"",INDEX(A,,5)&"","",INDEX(A,,12)))
The &"" removes 0s when NO NAME is present.
Respected Sir,
again thank you so much for your help and now i feel that you're a problem solver machine. its perfectly working
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks