How can I shift the data in columns to the left every Monday?

[Lesliejherrera]

I'm completely new to this, so don't mind my newbie lingo/lack of knowledge.

I have a report that I want to update automatically every week (Mondays) I'll explain everything... consider this a possible challenge. (it is for me)

Column A has the information or data that will be going in each row. (I have each cell in columns B through K feeding off info from a tab [FY2016].)

• Date of the week
• Shuttle Hours
• Union Regular Hours

• Union Overtime Hours

• Total Union Hours

• % Overtime Hours

• Avg Hrs Per Run

• Cases Per Stop

• Cases Per Truck

• Stops Per Truck

• CPMH

• Delivery Miles

• Shuttle Miles

• Total Fleet Miles

• Avg Delivery Miles/Trip

• Avg Total Miles/Trip

• Total Runs

• Cases Shipped

• Avg. Piece per Mile

• Total Scheduled Stops

• Shorts

• Miles Efficiency

• Roadnet Miles

Columns B through K have ending dates for each week, with K having the current week. so right know, its.

B:3 3/19/2016, C:3 3/26/2016, and so on until K.

Column L (also getting info from a different tab [FY2015]) has K's last year's week.

K:3 5/21/2016
L:3 5/23/2015

I upload information weekly. Every Monday I put in the previous weeks data ending on Saturday.

I need a formula or code...

That can shift the data to the left every Monday so the date will be "fresh." But I don't want to delete columns. I want to have the same amount of columns, only change the info based on the week.

The whole point of the report is to compare the "current" week with last years week.

Column M just subtracts L from K.

If it's not clear, that's mostly because I'm terrible at explaining things.

I would appreciate any help and am open to suggestions.

 

[Luke M]

A lot depends on how the numbers are getting there currently. Ideally, all the numbers in the attached image are the result of formulas doing lookups. In which case, you can make cell B3 the only cell you input a hard value in, and the other header columns do something like:
=B3+7
and then the data fields fill themselves in by doing the correct lookups from their respective sheets.

If your sheet is not laid out nicely with formulas, would have to know more about what the data source looks like, to see if we can make the analysis easier.

 

[Lesliejherrera]

So columns B through K are getting information from this tab. (FY2016)

Is lookup this:

I typed in cell
B:3 ='FY2016'!M3
C:3 ='FY2016'!N3
and so on until K (including it)

and every cell below each date is
B:6 ='FY2016'!M6
B:8 ='FY2016'!M8
B:9 ='FY2016'!M9
and so on until the bottom
and under date.

except
B:41=B42/B18
and C41 through K:41

L:3 ='FY2015'!V3


So then once the week is done the new dates would be
3/26/2016
4/2/2016
4/9/2016
4/16/2016
4/23/2016
4/30/2016
5/7/2016
5/14/2016
5/21/2016
5/28/2016

so then B:3 would now be ='FY2016'!N3
and everything underneath it N something.

C:3 ='FY2016'!O3
D:3 ='FY2016'!P3
and so on until K

and now L would be
L:3 ='FY2015'!W3

 

[Luke M]

No, a lookup type formula would be some sort of VLOOKUP or INDEX/MATCH combination. With a straight linking, my previous idea wont' work. With that layout, the easiest is to delete the column on left, and insert a column on the right (and copy formulas from left).

 

[bosco_yip]

Try............

1. Extract data from Sheet FY2016 to range B1:K42.

1.1] B1, formula copy across to K1:

=IFERROR(INDEX('FY2016'!1:1,0,MATCH(9.99E+307,'FY2016'!$6:$6)-10+COLUMNS($B1:B1)),"")

1.2] K2, enter : FY2016

1.3] B3, formula copy across to K3, and all copy down:

=IFERROR(1/LOOKUP(9.99E+307,1/OFFSET('FY2016'!$B3,0,0,1,MATCH(9.99E+307,'FY2016'!$6:$6)-11+COLUMNS($B3:B3))),"")

2. Extract data from Sheet FY2015 to range L1:L42

2.1] L1, formula :

=K1

2.2] L2, enter : FY2015

2.3] L3, formula copy down:

=IFERROR(1/(1/(INDEX('FY2015'!3:3,,MATCH(L$1,'FY2015'!$1:$1,0)))),"")

3. Difference of FY2016-FY2015 to range M1:M42

3.1] M2, enter : FY2016-FY2015

3.2] M6, formula copy down:

=IF(K6="","",K6-L6)

Remark :

When upload of Sheet FY2016 new information weekly, the sheet TRANS table will auto shift left with date in "fresh", and together with the FY2015 last years week.

Regards
Bosco