>> 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]

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.

The link to you file generates this message:

This item might not exist or is no longer available


This item might have been deleted, expired, or you might not have permission to view it. Contact the owner of this item for more information.

 

[collegeitdept]

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


Sorry.

 

[T. Valko]

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


Sorry.

OK, so exactly what do you want to do?

You posted this formula:

=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))),"")

But in your file there's nothing in cell U2. What is supposed to be in cell U2?

 

[collegeitdept]

OK, so exactly what do you want to do?

You posted this formula:


But in your file there's nothing in cell U2. What is supposed to be in cell U2?

No that formula was an example of a formula that can skip blank rows.... but it doesn't work with multiple variables to match against.


I need basically the same formula that skips blank rows... but with multiple variables (IE: Sumproduct instead of Index Match)... also that formula looks for an exact date... this new formula with sumproduct cannot and must be based on Weekday instead.

 

[T. Valko]

No that formula was an example of a formula that can skip blank rows.... but it doesn't work with multiple variables to match against.


I need basically the same formula that skips blank rows... but with multiple variables (IE: Sumproduct instead of Index Match)... also that formula looks for an exact date... this new formula with sumproduct cannot and must be based on Weekday instead.

OK, but you still didn't explain exactly what you want to do?

 

[collegeitdept]

Basically this spreadsheet will generate a list of centers based on today's weekday number, location, and number.



IE:


Today: Wednesday
Location: Lancaster - 9932
Number: 2 (column L)


Listings:


103
119
128
137
151
162
167
168
174
177

 

[T. Valko]

Basically this spreadsheet will generate a list of centers based on today's weekday number, location, and number.



IE:


Today: Wednesday
Location: Lancaster - 9932
Number: 2 (column L)


Listings:


103
119
128
137
151
162
167
168
174
177

Ok, still not sure if this is what you want but we'll go with it...

On some other sheet...

A2 = Lancaster - 9932
B2 = Wednesday
C2 = 2

Enter this array formula** in D2. This wll return the count of records that meet the criteria.

=SUM(IF('New Food'!B3:B627=A2,IF(INDEX('New Food'!E3:I627,0,MATCH(Sheet2!B2,TEXT('New Food'!E1:I1,"dddd"),0))<>"",IF('New Food'!L3:L627=C2,1))))

Enter this array formula** in E2:

=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))))

Copy down until you get blanks.

** 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]

Unfortunately the second formula does not work... it brings other center numbers.

 

[collegeitdept]

Please sheet 2....starting with B5 with the 2nd formula




=IF(ROWS(B$5:B5)>$A$2,"",INDEX('[- Centres by Warehouse.xls]New Food'!$A$3:$A$693,SMALL(IF('[- Centres by Warehouse.xls]New Food'!$B$3:$B$693=$A$3,IF(INDEX('[- Centres by Warehouse.xls]New Food'!$E$3:$I$693,0,MATCH(TEXT($L$1,"dddd"),TEXT('[- Centres by Warehouse.xls]New Food'!$E$1:$I$1,"dddd"),0))<>"",IF('[- Centres by Warehouse.xls]New Food'!$S$3:$S$693=$C$2,ROW('[- Centres by Warehouse.xls]New Food'!$S$3:$S$693)))),ROW(B$5:B5))))


It does not work for some reason.