Riddlemethis
New Member
- Joined
- Apr 20, 2021
- Messages
- 14
- Office Version
- 2013
- Platform
- Windows
ok, this should be simple but it's wrecking my brain.
I've a large sheet of data which contains delivery's made to different locations but the amount delivered depends on whether or not a promotion is being run (usually every 3rd week). I'm looking to get an average of stock delivered in the 3 week cycle so i have a table of dates of when the delivery was made and a seperate table which tells me where the date fell in the 3 week cycle. ( Spreadsheet attached is a slim down version)
I'm using vlookup to check the date against the table array on the separate sheet and looking it to return the weekly cycle but keep getting N/A...
Should i be using vlookup? or am i doing something dumb here.
* my formula in coloum k
I've a large sheet of data which contains delivery's made to different locations but the amount delivered depends on whether or not a promotion is being run (usually every 3rd week). I'm looking to get an average of stock delivered in the 3 week cycle so i have a table of dates of when the delivery was made and a seperate table which tells me where the date fell in the 3 week cycle. ( Spreadsheet attached is a slim down version)
I'm using vlookup to check the date against the table array on the separate sheet and looking it to return the weekly cycle but keep getting N/A...
Should i be using vlookup? or am i doing something dumb here.
* my formula in coloum k
Delivery Test.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Date | Day | Store No: | Store Name | Time Due | Arr Time | Start Unload | Item 1 Delivery amount | Item 2 Delivery amount | Item 3 Delivery amount | Cycle | ||
2 | 06/04/2020 | Monday | 2 | Location 1 | 07:50 | 07:15 | 07:20 | 13 | 1 | #N/A | |||
3 | 06/04/2020 | Monday | 2 | Location 1 | 09:15 | 09:10 | 09:15 | 13 | #N/A | ||||
4 | 20/04/2020 | Monday | 2 | Location 1 | 07:55 | 07:49 | 07:53 | 9 | 3 | #N/A | |||
5 | 20/04/2020 | Monday | 2 | Location 1 | 14:50 | 11:33 | 11:45 | 13 | #N/A | ||||
6 | 27/04/2020 | Monday | 2 | Location 1 | 07:50 | 07:54 | 07:57 | 10 | 1 | #N/A | |||
7 | 27/04/2020 | Monday | 2 | Location 1 | 14:00 | 12:15 | 12:20 | 20 | #N/A | ||||
8 | 04/05/2020 | Monday | 2 | Location 1 | 08:30 | 08:15 | 08:20 | 8 | 1 | #N/A | |||
9 | 04/05/2020 | Monday | 2 | Location 1 | 13:55 | 10:54 | 11:00 | 19 | #N/A | ||||
10 | 18/05/2020 | Monday | 2 | Location 1 | 08:00 | 08:07 | 08:10 | 8 | 2 | #N/A | |||
11 | 18/05/2020 | Monday | 2 | Location 1 | 13:00 | 11:57 | 12:00 | 10 | #N/A | ||||
12 | 25/05/2020 | Monday | 2 | Location 1 | 07:55 | 07:00 | 07:03 | 7 | 1 | #N/A | |||
13 | 25/05/2020 | Monday | 2 | Location 1 | 13:00 | 11:10 | 11:15 | 19 | #N/A | ||||
14 | 08/06/2020 | Monday | 2 | Location 1 | 07:55 | 07:35 | 07:40 | 8 | 1 | #N/A | |||
15 | 08/06/2020 | Monday | 2 | Location 1 | 14:00 | 11:30 | 11:35 | 13 | |||||
16 | 15/06/2020 | Monday | 2 | Location 1 | 07:50 | 07:05 | 07:05 | 5 | 2 | ||||
17 | 15/06/2020 | Monday | 2 | Location 1 | 13:05 | 12:20 | 12:30 | 20 | |||||
18 | 29/06/2020 | Monday | 2 | Location 1 | 08:00 | 07:45 | 07:50 | 7 | 2 | ||||
19 | 29/06/2020 | Monday | 2 | Location 1 | 13:00 | 12:15 | 12:20 | 18 | |||||
20 | 06/07/2020 | Monday | 2 | Location 1 | 07:45 | 07:07 | 07:10 | 7 | 1 | ||||
21 | 06/07/2020 | Monday | 2 | Location 1 | 13:00 | 11:41 | 11:50 | 27 | |||||
22 | 13/07/2020 | Monday | 2 | Location 1 | 08:05 | 07:20 | 07:55 | 2 | |||||
23 | 13/07/2020 | Monday | 2 | Location 1 | 13:50 | 11:40 | 12:00 | 3 | |||||
24 | 20/07/2020 | Monday | 2 | Location 1 | 07:50 | 07:05 | 07:10 | 6 | 1 | ||||
Deliveries |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K2:K14 | K2 | =VLOOKUP(A2,Cycle!$A$1:$B$476,2,FALSE) |
B2:B24 | B2 | =TEXT(A2,"dddd") |
Delivery Test.xlsx | ||||
---|---|---|---|---|
A | B | |||
89 | 29/03/2020 | Promo | ||
90 | 30/03/2020 | Promo | ||
91 | 31/03/2020 | Promo | ||
92 | 01/04/2020 | Promo | ||
93 | 02/04/2020 | Promo | ||
94 | 03/04/2020 | Promo | ||
95 | 04/04/2020 | Promo | ||
96 | 05/04/2020 | After Promo | ||
97 | 06/04/2020 | After Promo | ||
98 | 07/04/2020 | After Promo | ||
99 | 08/04/2020 | After Promo | ||
100 | 09/04/2020 | After Promo | ||
101 | 10/04/2020 | After Promo | ||
102 | 11/04/2020 | After Promo | ||
103 | 12/04/2020 | Pre-Promo | ||
104 | 13/04/2020 | Pre-Promo | ||
105 | 14/04/2020 | Pre-Promo | ||
106 | 15/04/2020 | Pre-Promo | ||
107 | 16/04/2020 | Pre-Promo | ||
108 | 17/04/2020 | Pre-Promo | ||
109 | 18/04/2020 | Pre-Promo | ||
110 | 19/04/2020 | Promo | ||
111 | 20/04/2020 | Promo | ||
112 | 21/04/2020 | Promo | ||
113 | 22/04/2020 | Promo | ||
114 | 23/04/2020 | Promo | ||
115 | 24/04/2020 | Promo | ||
116 | 25/04/2020 | Promo | ||
117 | 26/04/2020 | After Promo | ||
118 | 27/04/2020 | After Promo | ||
119 | 28/04/2020 | After Promo | ||
120 | 29/04/2020 | After Promo | ||
121 | 30/04/2020 | After Promo | ||
122 | 01/05/2020 | After Promo | ||
123 | 02/05/2020 | After Promo | ||
124 | 03/05/2020 | Pre-Promo | ||
125 | 04/05/2020 | Pre-Promo | ||
126 | 05/05/2020 | Pre-Promo | ||
127 | 06/05/2020 | Pre-Promo | ||
128 | 07/05/2020 | Pre-Promo | ||
129 | 08/05/2020 | Pre-Promo | ||
130 | 09/05/2020 | Pre-Promo | ||
Cycle |