Pipedream? Will excel automatically reposition columns?

[carolvshimmer]

I'm working on a monthly schedule recap, the months go across the first row, the first column has everybodys' names. The schedule has many references to other sheets in the workbook. What I want to happen is for the most recent month that has passed to be the first one to the left of everybodys' names, and then the months go down in order. So this has to change every month. Is there anyway to get Excel to automatically move the columns containing the prior month to the end of the row and then set up a new column/set of columns for the most recent month? Basically I want to get out of a lot of copying and pasting

Hope this makes sense.....thanks

 

[Yogi Anand]

I'm working on a monthly schedule recap, the months go across the first row, the first column has everybodys' names. The schedule has many references to other sheets in the workbook. What I want to happen is <font color="red">for the most recent month that has passed to be the first one to the left of everybodys' names, and then the months go down in order</font>. So this has to change every month. Is there anyway to get Excel to automatically move the columns containing the prior month to the end of the row and then set up a new column/set of columns for the most recent month? Basically I want to get out of a lot of copying and pasting

Hope this makes sense.....thanks

Hi Carol:

Please refer to the part of your statement I have red fonted ... do you mean the for the most recent month that has passed to be the first one to the right of everybodys' names ....

or am I missing something else here?

 

[Legacy 12630]

Try recording a macro to do what you need.

 

[carolvshimmer]

yep I meant right, thanks for catching that. never was good with left and right

 

[Yogi Anand]

Hi Carol:

Let us have a look at the following ...

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1	MostRecentMonth	next1	next2	next3	next4	next5	next6	next7	next8	next9	next10	next11	next12	next13
2	Name	12	1	2	3	4	5	6	7	8	9	10	11	12	1
3	Carol
4	Becky
5	Joe
Sheet4

formula in cell A1 is ...=month(now())

and my convoluted formula in cell B2 is ...

=MOD($A$1-1+COLUMNS($A:A)-1,12)+(MOD($A$1-1+COLUMNS($A:A)-1,12)=0)*12
this is then copied across. I hope this helps!

 

[PA HS Teacher]

Here How I would have done it.
I would have kept the actual monthly recap information on a separate sheet, but adding the following formula and pasted across.

This formula tells you how many months this was before the Month in cell A1. (or must make it Month(now()))

=IF(MONTH(B2)-MONTH($A$1)<=0,MONTH(B2)-MONTH($A$1),MONTH(B2)-MONTH($A$1)-12)

Book1
	A	B	C	D	E	F
1	6/15/2004	-5	-4	-3	-2	-1
2	Names	1-Jan	1-Feb	1-Mar	1-Apr	1-May
3	Carol	Data	To			Be
4	Becky		Exam	Random		Or
5	Joe				FoolsDay	Not
Schedule Recap

On another page, I would then use an Hlookup to display the information for this month, then 1 month ago etc.
I used the following:
=HLOOKUP(0,Recap,ROW(),0)

Where Recap is the original information, and the Row with the 0,-1,-2 etc. is the first Row.

Book1
	A	B	C	D	E
1		ThisMonth	LastM	2ago	3ago
2		1-Jun	1-May	1-Apr	1-Mar
3	Names	0	Be	0	0
4	Carol	0	Or	0	Random
5	Becky	Items	Not	FoolsDay	0
6	Joe	0	0	0	0
Recent Months Left