How do I 'nudge' data over depending on a transit time

mickyd67

Board Regular
Joined
Jul 13, 2011
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Hi all

I have a number of countries I ship product to and have 12 columns, one for each month, showing the dispatch quantity for that given month

Each country has a different transit time - some are 1 month, some can be 3 months etc

I want to have another 12 columns of data that shows the 'arrival month' based on the transit time.

Is there a way to do that so I can take the dispatch quantity in, say, April and have it show in May based on it being a 1 month transit time?

The April dispatch quantity is in Cell M7 and the arrival month should be in Cell AD7 (May)
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I think this may work for you. But, it is pretty clunky, there maybe more succinct formulas.
It also requires a "Starting Date" cell. Which you could eliminate by putting a date calculation in each cell, but I find this easier.

Cell Formulas
RangeFormula
C2:N2C2="Disp Qty " & TEXT(EDATE($B$1,COLUMN()-3),"mmm")
O2:Z2O2="Arriv Qty " & TEXT(EDATE($B$1,COLUMN()-3),"mmm")
O3:Z5O3=IF(MONTH(EDATE($B$1,COLUMN()-3))-$B3<1,"",INDEX($C$3:$N$3,1,MONTH(EDATE($B$1,COLUMN()-3))-$B3))
 
Upvote 0
Hi there - many thanks for this. I must admit I'm struggling a little and wonder if there are any other formulas / options out there to make this a bit easier?
 
Upvote 0
My thought is to retool your worksheet and have a lookup table for your shipping times. and then create the data like this.
If you need a cross tab report you can build it off of this in another worksheet:

(I'm sure there are many different ways to do this, and I may do it differently if I looked at it tomorrow. the OFFSET function, though volatile is probably another tool to use.)

Book1
ABCDEFGH
1CountryMonthDisp QtyArriv QtyCountryTransit Time (Months)
2UKJan38 UK1
3UKFeb2738USA2
4UKMar4127AUS3
5UKApr4341
6UKMay2743
7UKJun4427
8UKJul3044
9UKAug4430
10UKSep4344
11UKOct2243
12UKNov3122
13UKDec3431
14USAJan27 
15USAFeb32 
16USAMar3427
17USAApr4332
18USAMay5034
19USAJun3443
20USAJul3850
21USAAug2334
22USASep3438
23USAOct3223
24USANov4434
25USADec2832
26AUSJan25 
27AUSFeb33 
28AUSMar27 
29AUSApr3725
30AUSMay3733
31AUSJun4527
32AUSJul2037
33AUSAug3437
34AUSSep2745
35AUSOct3920
36AUSNov2734
37AUSDec2427
Sheet3
Cell Formulas
RangeFormula
D2:D37D2=IF(MONTH(B2)<(XLOOKUP(A2,$G$2:$G$4,$H$2:$H$4,0)),"", TAKE(FILTER($A$2:$C$37, ($A$2:$A$37=$A2)* ($B$2:$B$37=EDATE($B2,-XLOOKUP(A2,$G$2:$G$4,$H$2:$H$4,0))),""),,-1))
 
Upvote 0
wonder if there are any other formulas / options out there to make this a bit easier?
I wonder if you would find it easier if you turned it into a simple index/match search. The approach is as follows:
1. Months go from 1 to 12 to make it easy to calculate
2. Add a column with the transit time in months. Any number you enter there applies to the entire row.
3. In the arrival side, you enter an index/match formula. You want to return the value where
current month - arrival time in months = quantity in the shipping side
So if you have an arrival time of 1 months, and you entered a quantity in april, then:
april - 1 months = return quantity in march returns march's quantity
may - 1 months = return quantity in april returns april's quantity

That looks like this:
Libro1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1
2
3
4
5EneFebMarAbrMayJunJulAgoSepOctNovDicEneFebMarAbrMayJunJulAgoSepOctNovDic
6Transit months123456789101112123456789101112
71999#N/A0009990000000
82999#N/A#N/A000999000000
92999#N/A#N/A000999000000
102999#N/A#N/A000999000000
113999#N/A#N/A#N/A00099900000
124999#N/A#N/A#N/A#N/A0009990000
1310999#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A00
No Month cycling
Cell Formulas
RangeFormula
Z7:AK13Z7=INDEX($J7:$U7,MATCH(Z$6-$A7,$J$6:$U$6,0))


However, we quickly encounter a problem. If the arrival starts in a new year, we run out of month columns, we need a way to cycle in a way that the month after december is january. You do that using MOD. So your formula should be slightly modified as follows:
Libro1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1
2
3
4
5EneFebMarAbrMayJunJulAgoSepOctNovDicEneFebMarAbrMayJunJulAgoSepOctNovDic
6Transit months123456789101112123456789101112
7199900009990000000
8299900000999000000
9299900000999000000
10299900000999000000
11399900000099900000
12499900000009990000
131099909990000000000
Month cycling
Cell Formulas
RangeFormula
Z7:AK13Z7=INDEX($J7:$U7,MATCH(MOD(Z$6-$A7-1,12)+1,$J$6:$U$6,0))
 
Upvote 0
@Edgar_ ,
As I said in my earlier post there are many way to accomplish this.
However, I'm a fan of keeping input data and row relevant calculated fields separate from reporting whenever possible.

Take a look at his video just posted yesterday by Minda Treacy at MyOnlineTrainingHub .

 
Upvote 0
As I said in my earlier post there are many way to accomplish this.
However, I'm a fan of keeping input data and row relevant calculated fields separate from reporting whenever possible.
I call that "stop doing data entry on your reports". Excel users often fall in that trap. I'm sure I've talked about that in all the few threads I've responded, but the OP seems to have a working worksheet already.

He may just need a formula to nudge his quantities, so the approach is adequate.

Shall the worksheet design change, I'd suggest a worksheet with a ListObject table for the countries, another for the countries and transit times, another for the products, and another for the shipping, with calculated arrivals. A pivot table or something else would take care of the view.
 
Upvote 0
in my world whenever possible reports don't exist before data entry. :)

I gave OP a suggested solution very similar to yours in POST #2, and OP responded by asking for something simpler.
Un-pivoting cross tab semi-reports into tablulated data is a lot simpler these days with Power Query.

All other reporting in many different views and perspectives is much easier.
 
Upvote 0
You're free to suggest whatever you want, man. I don't have a problem with your suggestions, my approach was to just work within the OP's constraints the simplest I could imagine and a simple index/match is simpler than, say, having to learn the whole Power whatever there is in store right now.
 
Upvote 0
Hi everyone - thanks for your help I'll have a look and see what one works best for me :)
 
Upvote 0

Forum statistics

Threads
1,224,243
Messages
6,177,386
Members
452,773
Latest member
D P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top