>> Skip blank across rows - Multiple Variables

[collegeitdept]

View data source here:

https://skydrive.live.com/redir.aspx...ATK6iYb-fMg5LA


I need a formula like the SUMPRODUCT (multiple criteria) then skip the blanks (rows) cells.

ie: =IF(ROWS(U$3:U3)<=$U$2,INDEX($A$3:$A$18,
SMALL(IF(1-(INDEX($E$3:$I$18,0,
MATCH(U$1,TEXT($E$1:$I$1,"dddd"),0))=""),
ROW($A$3:$A$18)-ROW($A$3)+1),ROWS(U$3:U3))),"")



The formula i need would be to list only the centers for the Weekday Wednesday (Column G) based on Location variable (Column B) and Number (Column C).



Main Worksheet:

Column A Column B
Today Day: weekday(today)
Location: Lancaster - 9932
Number: 3300708

Formula listings...




For the weekday (wednesday Column G) variable... it will also have change based on today weekday number.... So on Thursday it will have to search for Thursday on the data sheet which is Column H.



Thanks.

 

[T. Valko]

They are simply default dates (weekday(2 - 6)).... again the date in the formula is a variable based on whatever weekday it is now.

Here's your file...

http://cjoint.com/?BBkali3kFCj

All of the formulas are highlighted.

The formulas in column A and F3 are array formulas.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[collegeitdept]

Here's your file...

http://cjoint.com/?BBkali3kFCj

All of the formulas are highlighted.

The formulas in column A and F3 are array formulas.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thank you..



Would you be able to post this to MSN Sky drive or another site... that site is blocked by my company.

Thanks.

 

[T. Valko]

Thank you..



Would you be able to post this to MSN Sky drive or another site... that site is blocked by my company.

Thanks.

Ok, I'll just explain it...

On Sheet2...

A1 thru F1 are the following column Headers:

A1 = Location
B1 = Manager
C1 = Date
D1 = Weekday
E1 = Day
F1 = Count

A2 = some location like Lancaster - 9932
B2 = some manager like ER
C2 = some date like 28/2012
D2 = formula:

=TEXT(C2,"dddd")

E2 = formula:

=WEEKDAY(C2,2)

F2 = array formula**:

=COUNT(IF('New Food'!B3:B627=A2,IF('New Food'!S3:S627=B2,INDEX('New Food'!E3:I627,0,E2))))

This array formula** entered in A5 to return the center numbers:

=IF(ROWS(A$5:A5)>F$2,"",INDEX('New Food'!A:A,SMALL(IF('New Food'!B$3:B$627=A$2,IF('New Food'!S$3:S$627=B$2,IF(INDEX('New Food'!E$3:I$627,0,E$2)<>"",ROW('New Food'!A$3:A$627)))),ROWS(A$5:A5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[collegeitdept]

Ok, I'll just explain it...

On Sheet2...

A1 thru F1 are the following column Headers:

A1 = Location
B1 = Manager
C1 = Date
D1 = Weekday
E1 = Day
F1 = Count

A2 = some location like Lancaster - 9932
B2 = some manager like ER
C2 = some date like 28/2012
D2 = formula:

=TEXT(C2,"dddd")

E2 = formula:

=WEEKDAY(C2,2)

F2 = array formula**:

=COUNT(IF('New Food'!B3:B627=A2,IF('New Food'!S3:S627=B2,INDEX('New Food'!E3:I627,0,E2))))

This array formula** entered in A5 to return the center numbers:

=IF(ROWS(A$5:A5)>F$2,"",INDEX('New Food'!A:A,SMALL(IF('New Food'!B$3:B$627=A$2,IF('New Food'!S$3:S$627=B$2,IF(INDEX('New Food'!E$3:I$627,0,E$2)<>"",ROW('New Food'!A$3:A$627)))),ROWS(A$5:A5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Using this formula from the past (I managed to get your original formula to work)....


=IF(ROWS(E$2:E2)>D$2,"",INDEX('New Food'!A:A,SMALL(IF('New Food'!B$3:B$627=A$2,IF(INDEX('New Food'!E$3:I$627,0,MATCH(B$2,TEXT('New Food'!E$1:I$1,"dddd"),0))<>"",IF('New Food'!L$3:L$627=C$2,ROW('New Food'!L$3:L$627)))),ROWS(E$2:E2))))



Can allow one more variable? Either 1 or 2 found in the datasheet Column R...


https://skydrive.live.com/view.aspx...hecklist1.xlsx?cid=71f4294719610d9e&app=Excel



THis is to designate on/off weeks...


Thanks for your help.

 

[collegeitdept]

Ignore the post above...


I uploaded the spreadsheet you provided me.... I have one last question... and it may be a little more difficult.



https://skydrive.live.com/redir.aspx?cid=71f4294719610d9e&resid=71F4294719610D9E!176&parid=undefined




From you spreadsheet I added one more Worksheet called Deposits... this is when each center is scheduled to place deposits into their account.


On your Sheet 2 with all the formulas... the first section (you created) is for the customer orders... i have a section below that needs a formula that will also list all the centers in that same region, today's weekday, manager, AND then if today's their on/off week (1 or 2).


However, if the center is already listed in the above order section... no need to redisplay that center. Only display if the center is not already listed. (starting at Cell B24).

On the Order section (the part you created)...starting at B5... on the next column over (C) i need an if formula... if that center also must place a deposit on that day... if so "Deposit".



Thanks.

 

[collegeitdept]

Alright I found how to add another variable:


This is the formula:

=IF(ROWS(A$37:A37)>S$36,"",INDEX('[- Centres by Warehouse.xls]Deposits'!A:A,SMALL(IF('[- Centres by Warehouse.xls]Deposits'!$B$2:$B$692=A$3,IF(INDEX('[- Centres by Warehouse.xls]Deposits'!$E$2:$I$692,0,MATCH(WEEKDAY(K$1),'[- Centres by Warehouse.xls]Deposits'!$E$1:$I$1,0))<>"",IF('[- Centres by Warehouse.xls]Deposits'!$C$2:$C$692=C$1,IF('[- Centres by Warehouse.xls]Deposits'!$J$2:$J$692=$P$1,ROW('[- Centres by Warehouse.xls]Deposits'!$A$2:$A$692))))),ROWS(A$37:A37))))


(if you see any errors...please don't hesitate to correct it)


and it works fine so far.


However, I need to add to that formula so that Excel will EXCLUDE center numbers already mentioned in the above section (rows A5:A34).... do you know how do that???

 

[T. Valko]

Alright I found how to add another variable:


This is the formula:

=IF(ROWS(A$37:A37)>S$36,"",INDEX('[- Centres by Warehouse.xls]Deposits'!A:A,SMALL(IF('[- Centres by Warehouse.xls]Deposits'!$B$2:$B$692=A$3,IF(INDEX('[- Centres by Warehouse.xls]Deposits'!$E$2:$I$692,0,MATCH(WEEKDAY(K$1),'[- Centres by Warehouse.xls]Deposits'!$E$1:$I$1,0))<>"",IF('[- Centres by Warehouse.xls]Deposits'!$C$2:$C$692=C$1,IF('[- Centres by Warehouse.xls]Deposits'!$J$2:$J$692=$P$1,ROW('[- Centres by Warehouse.xls]Deposits'!$A$2:$A$692))))),ROWS(A$37:A37))))


(if you see any errors...please don't hesitate to correct it)


and it works fine so far.


However, I need to add to that formula so that Excel will EXCLUDE center numbers already mentioned in the above section (rows A5:A34).... do you know how do that???

The "simple" formula sure has grown into a real monster formula!

Add another test like this...

...IF(ISNA(MATCH('[- Centres by Warehouse.xls]Deposits'!A$2:A$692,A$5:A$34,0)),...

 

[collegeitdept]

The "simple" formula sure has grown into a real monster formula!

Add another test like this...

...IF(ISNA(MATCH('[- Centres by Warehouse.xls]Deposits'!A$2:A$692,A$5:A$34,0)),...

Can you please place it into the original formula??


Every time I try it out it comes up with an error...

I guess what i mean is how do I do that?

 

[T. Valko]

Can you please place it into the original formula??


Every time I try it out it comes up with an error...

I guess what i mean is how do I do that?

Like this...

=IF(ROWS(A$37:A37)>S$36,"",INDEX('[- Centres by Warehouse.xls]Deposits'!A:A,SMALL(IF(ISNA(MATCH('[- Centres by Warehouse.xls]Deposits'!A$2:A$692,A$5:A$34,0)),IF('[- Centres by Warehouse.xls]Deposits'!$B$2:$B$692=A$3,IF(INDEX('[- Centres by Warehouse.xls]Deposits'!$E$2:$I$692,0,MATCH(WEEKDAY(K$1),'[- Centres by Warehouse.xls]Deposits'!$E$1:$I$1,0))<>"",IF('[- Centres by Warehouse.xls]Deposits'!$C$2:$C$692=C$1,IF('[- Centres by Warehouse.xls]Deposits'!$J$2:$J$692=$P$1,ROW('[- Centres by Warehouse.xls]Deposits'!$A$2:$A$692)))))),ROWS(A$37:A37))))

 

[collegeitdept]

Like this...

=IF(ROWS(A$37:A37)>S$36,"",INDEX('[- Centres by Warehouse.xls]Deposits'!A:A,SMALL(IF(ISNA(MATCH('[- Centres by Warehouse.xls]Deposits'!A$2:A$692,A$5:A$34,0)),IF('[- Centres by Warehouse.xls]Deposits'!$B$2:$B$692=A$3,IF(INDEX('[- Centres by Warehouse.xls]Deposits'!$E$2:$I$692,0,MATCH(WEEKDAY(K$1),'[- Centres by Warehouse.xls]Deposits'!$E$1:$I$1,0))<>"",IF('[- Centres by Warehouse.xls]Deposits'!$C$2:$C$692=C$1,IF('[- Centres by Warehouse.xls]Deposits'!$J$2:$J$692=$P$1,ROW('[- Centres by Warehouse.xls]Deposits'!$A$2:$A$692)))))),ROWS(A$37:A37))))

It doesn't work.... says too many nested formulas.