Hi, hopefully somebody can help in some way.
I have this formula: =IFERROR(INDEX(Customer_Weight_by_Date[Name_Weight],MATCH(1,(Customer_Weight_by_Date[tblOrderHeader.REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Customer_Weight_by_Date[Route]),0)),0)
which looks a this table:
And gives the result below...
You can see in route 15, DRENCH EUROP in E9 and F9, but I need a way to see the second (or nth) entry for that route on that date.
(hope that makes sense, I want to see MULTI-TURN L 296 in F9)
TIA
I have this formula: =IFERROR(INDEX(Customer_Weight_by_Date[Name_Weight],MATCH(1,(Customer_Weight_by_Date[tblOrderHeader.REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Customer_Weight_by_Date[Route]),0)),0)
which looks a this table:
tblOrderHeader.REQUIRED_DATE | Route | Customer Weight by Date | Short_Name | Name_Weight | |
17/04/2023 | 1 | 1158 | SOUTHERN COU | SOUTHERN COU 1158 | |
17/04/2023 | 5 | 1118 | HERMITAGE GL | HERMITAGE GL 1118 | |
17/04/2023 | 15 | 147 | DRENCH EUROP | DRENCH EUROP 147 | |
17/04/2023 | 15 | 296 | MULTI-TURN L | MULTI-TURN L 296 | |
And gives the result below...
You can see in route 15, DRENCH EUROP in E9 and F9, but I need a way to see the second (or nth) entry for that route on that date.
(hope that makes sense, I want to see MULTI-TURN L 296 in F9)
TIA
Copy of Copy of Daily Weights.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Mon 17 Apr | |||||||
2 | Route | Area | Weight | Drops | ||||
3 | 1 | Horsham | 1158 | 1 | SOUTHERN COU 1158 | |||
4 | 3 | A3 | 314 | 8 | 0 | |||
5 | 30 | A3 | 257 | 3 | 0 | |||
6 | 5 | Basingstoke | 1387 | 6 | HERMITAGE GL 1118 | |||
7 | 6 | Salisbury | 208 | 4 | 0 | |||
8 | 88 | Showerpower | 174 | 1 | 0 | |||
9 | 15 | Drench/Multi | 443 | 2 | DRENCH EUROP 147 | DRENCH EUROP 147 | ||
10 | 17 | Maxlen | 109 | 1 | 0 | |||
Front |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | B1 | =TEXT(TODAY()-WEEKDAY(TODAY(),2)+1,"ddd dd mmm") |
C3:C9 | C3 | =SUMIFS(tblOrderHeader[TOTAL_WEIGHT],tblOrderHeader[Route],A3,tblOrderHeader[REQUIRED_DATE],TODAY()-WEEKDAY(TODAY(),2)+1) |
D3:D9 | D3 | =IFERROR(INDEX(Merge1[Count],MATCH(1,(Merge1[REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Merge1[Route]),0)),0) |
E3:E8,E10,E9:F9 | E3 | =IFERROR(INDEX(Customer_Weight_by_Date[Name_Weight],MATCH(1,(Customer_Weight_by_Date[tblOrderHeader.REQUIRED_DATE]=TODAY()-WEEKDAY(TODAY(),2)+1)*($A3=Customer_Weight_by_Date[Route]),0)),0) |
C10 | C10 | =IFERROR(ROUND(JLOOKUP(TODAY()-WEEKDAY(TODAY(),2)+1,tblPurchaseOrderHeader[REQUIRED_DATE],tblPurchaseOrderHeader[Count]),0),0) |
D10 | D10 | =IF(C10>0,1,0) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
C3:C10,C13:C21,C24:C32,C35:C42,C45:C50,C55:C62,C65:C73,C76:C84,C87:C94,C97:C102,C107:C114,C117:C125,C128:C136,C139:C146,C149:C154,C159:C166,C169:C177,C180:C188,C191:C198,C201:C206 | Cell Value | >1200 | text | YES |
C3:C10,C13:C21,C24:C32,C35:C42,C45:C50,C55:C62,C65:C73,C76:C84,C87:C94,C97:C102,C107:C114,C117:C125,C128:C136,C139:C146,C149:C154,C159:C166,C180:C188,C191:C198,C201:C206,C169:C177 | Other Type | Color scale | NO |