Expanding a formula to display content on more variables.

[avidcat]

I am looking to expand a formula to display content on more variables.

I have a excel workbook already setup:
http://www.cashierassistant.com/ACTC...g-Current.xlsx

On the sheet "Search" a user is able to select from a drop down menu a department and it will return results from the "Data" Sheet for that department.

On the new workbook:
http://www.cashierassistant.com/ACTC...-Expanded.xlsx

I am looking to add more varianbles; Cells B4 - B9 of the sheet "Search".

The formula found in A16:N1000 on the "Search" Sheet would need to be edited:
=IF(OR(ROWS($B$9:$B9)>$B$6,$B$2="",COUNTBLANK($B$2)=1),"",INDEX(Data!A:A,MATCH(ROWS($B$9:$B9),Data!$O:$O,0)))

Along with the formula found in O3:O1000:
=IF(Search!$B$3="",IF(Search!$B$2="All",SUMPRODUCT(--(M$3:M3>=Search!$B$4),--(M$3:M3<=Search!$B$5)),SUMPRODUCT(--(A$3:A3=Search!$B$2),--(M$3:M3>=Search!$B$4),--(M$3:M3<=Search!$B$5))),IF(Search!$B$2="ALL",SUMPRODUCT(--(M$3:M3>=TODAY()-Search!$B$3),--(M$3:M3<=TODAY())),SUMPRODUCT(--(A$3:A3=Search!$B$2),--(M$3:M3>=TODAY()-Search!$B$3),--(M$3:M3<=TODAY()))))


I had help making these formulas to begin with, and it would need help yet again adjusting them.

I really appreciate any help.

Thank you,
Nick
Thanks Paul for the info. A copy of this post has also been posted here:
http://www.mrexcel.com/forum/showthr...more-variables

[NBVC]

Try revising the formula in O3 of the Data sheet to:

=IF(AND(OR(Search!$B$2="ALL",AND(A3=Search!$B$2,OR(Search!$B$3="",C3=Search!$B$3))),OR(Search!$B$4="All",AND(D3=Search!$B$4,OR(Search!$B$5="",F3=Search!$B$5))),OR(Search!$B$6="ALL",J3=Search!$B$6),OR(Search!$B$7="ALL",K3=Search!$B$7),OR(Search!$B$8="ALL",L3=Search!$B$8),OR(Search!$B$9="",ISNUMBER(SEARCH(Search!$B$9,N3))),OR(AND(Search!$B$10="",M3>=Search!$B$11,M3<=Search!$B$12),AND(ISNUMBER(Search!$B$10),M3>=TODAY()-Search!$B$10,M3<=TODAY()))),COUNT(O$2:O2)+1,"")

which should be much more efficient than using SUMPRODUCT.

The formula in the SEARCH sheet can remain untouched.

[avidcat]

That's pretty cool NBVC.

I am not getting the Shopper EID in cell B3 of the Search Sheet to work when All is selected in B2.

If I change B2 to anything but All, it seems to work.

I attached an updated excel with your formula.

Thanks for your help on this.

Nick

[NBVC]

I had assumed that if you select ALL, then you would not enter a Shopper EID or similarly, a TM EID or you would want to ignore them if entered.....

revise formula to:

=IF(AND(OR(AND(Search!$B$2="ALL",OR(Search!$B$3="",C3=Search!$B$3)),AND(A3=Search!$B$2,OR(Search!$B$3="",C3=Search!$B$3))),OR(AND(Search!$B$4="All",OR(Search!$B$5="",F3=Search!$B$5)),AND(D3=Search!$B$4,OR(Search!$B$5="",F3=Search!$B$5))),OR(Search!$B$6="ALL",J3=Search!$B$6),OR(Search!$B$7="ALL",K3=Search!$B$7),OR(Search!$B$8="ALL",L3=Search!$B$8),OR(Search!$B$9="",ISNUMBER(SEARCH(Search!$B$9,N3))),OR(AND(Search!$B$10="",M3>=Search!$B$11,M3<=Search!$B$12),AND(ISNUMBER(Search!$B$10),M3>=TODAY()-Search!$B$10,M3<=TODAY()))),COUNT(O$2:O2)+1,"")

[avidcat]

Thanks NBVC, that is just what I wanted.

I just noticed that the custom date range is not working, when I entered a custom date it returns a #VALUE! error. Worst case I can remove the custom date range if it would not work with the formula.

thanks,
Nick

[NBVC]

I apologize, I didn't test that aspect...

try:

=IF(AND(OR(AND(Search!$B$2="ALL",OR(Search!$B$3="",C3=Search!$B$3)),AND(A3=Search!$B$2,OR(Search!$B$3="",C3=Search!$B$3))),OR(AND(Search!$B$4="All",OR(Search!$B$5="",F3=Search!$B$5)),AND(D3=Search!$B$4,OR(Search!$B$5="",F3=Search!$B$5))),OR(Search!$B$6="ALL",J3=Search!$B$6),OR(Search!$B$7="ALL",K3=Search!$B$7),OR(Search!$B$8="ALL",L3=Search!$B$8),OR(Search!$B$9="",ISNUMBER(SEARCH(Search!$B$9,N3))),OR(IF(ISNUMBER(Search!$B$10),AND(M3>=TODAY()-Search!$B$10,M3<=TODAY()),AND(M3>=Search!$B$11,M3<=Search!$B$12)))),COUNT(O$2:O2)+1,"")

[avidcat]

Fantastic! Works beautifully. I really appreciate all the help you put into this.

Thanks,
Nick