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

 

[collegeitdept]

Essentially i need a formula to filter out all the irrelevant centers...

And list only centers for today's weekday, Location, and number (actually name abbreviation Column S)).

 

[T. Valko]

Essentially i need a formula to filter out all the irrelevant centers...

And list only centers for today's weekday, Location, and number (actually name abbreviation Column S)).

Column S? There is no data in column S.

When you say: "today's weekday", you mean cells that are not empty in the column that matches today's weekday, right? So, if today is Wednesday than that column would be column G, right?

 

[collegeitdept]

Column S? There is no data in column S.

When you say: "today's weekday", you mean cells that are not empty in the column that matches today's weekday, right? So, if today is Wednesday than that column would be column G, right?

YEs scroll over to the right... in Column S contains initials of managers.
(New Food tab).


There are three variables:

Weekday (columns $E-$I)
Location (column $B)
Initials of Managers (column $S)


On Sheet2 where the formula (list of centers will be)
Date: Cell $L$1
Location: $A$3
Initials of Managers: $C$2

 

[T. Valko]

YEs scroll over to the right... in Column S contains initials of managers.
(New Food tab).


There are three variables:

Weekday (columns $E-$I)
Location (column $B)
Initials of Managers (column $S)


On Sheet2 where the formula (list of centers will be)
Date: Cell $L$1
Location: $A$3
Initials of Managers: $C$2

In the file I downloaded there is no data in column S.

 

[collegeitdept]

In the file I downloaded there is no data in column S.

It's been updated... for some reason it was missing columns...

Go back to that link please.


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



Thanks for your help.

 

[collegeitdept]

T. Valko,

Were you able to look into this please?

 

[T. Valko]

T. Valko,

Were you able to look into this please?

I'm just now getting to it. Give me a few minutes.

 

[collegeitdept]

Thank you.

 

[T. Valko]

Thank you.

On the New Food sheet...

The dates in E1:I1, are those supposed to be the Monday thru Friday dates of the current week? In the file they are dates from July 2011 and the date criteria on Sheet2 cell L1 is 2/8/2012.

 

[collegeitdept]

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