#1   Report Post  
Posted to microsoft.public.excel.misc
jinvictor
 
Posts: n/a
Default auto change line


what i want is:

a1-a48 different names
b1-n1 different shifts, etc: morning shift, afternoon shift,
night shift, rdo
b2-n2 different shifts again,different as b1-n1
all the way to line 48

every saturday fornight week, we work drop 1 line, say if my name in a1
this fornight, saturday 2 weeks later, my name will be on line 2(b2-n2).
and person in a48 will be in a1.
how can i do it so every 2nd saturday when i open the workbook, it
automaticaly change to new line which i suppose to be.

thanx


--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=548256

  #2   Report Post  
Posted to microsoft.public.excel.misc
Zygan
 
Posts: n/a
Default auto change line


if i understand correctly the lines B2 - N48 stay the same and the A
column just moves down one and the bottom one goes to the top ?
If so put this code in and all you will have to do is run it every
saturday


Sub name_movement()

Range("A48").Select
Application.CutCopyMode = False
Selection.Cut
Range("A49").Select
ActiveSheet.Paste
Range("A1:A47").Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Range("A49").Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
End Sub
basically what it does is select a48 and puts it into a49 (so as you
dont lose it) the a1:a47 cuts this info and paste into a2:a49 then puts
the original a48 (now in a49) into cell a1 and all done

p.s did not really grasp your idea around the spreadshees maybe if this
dont help you could explain it further


--
Zygan
------------------------------------------------------------------------
Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
View this thread: http://www.excelforum.com/showthread...hreadid=548256

  #3   Report Post  
Posted to microsoft.public.excel.misc
jinvictor
 
Posts: n/a
Default auto change line


it is very helpful, but another problem is how can i make it do that
every secound saturday?

thanx for your time.


--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=548256

  #4   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default auto change line

Here's a play using non-array formulas ..

Sample construct available at:
http://www.savefile.com/files/5315921
48 names - Fortnightly Saturdays rotation schedule

In a sheet: Names, list the 48 names in A1:A48
Put in A49: =A1
Copy A49 down to A96 to repeat the namelist

In another sheet: X

Fill the numbers 1 - 48 across in A1:AV1
(these numbers will be the 48 roster #s)

Put in A2: =OFFSET(INDIRECT("'Names'!A"&ROW(A1)),COLUMN(A1)-1,)
Copy A2 across 48 cols to AV2, fill down to AV49 to populate the schedule
A2:AV49 will return the required 48 rotational rosters / schedules

In a new sheet: Y,

Put in A1: 10-Jun-2006
Put in A2: 24-Jun-2006 (the next fortnight Sat)

Select A1:A2, fill down to say, A96
to fill 2 cycle's worth of fortnight Saturdays till 30-Jan-2010

Put in B1: =MOD(ROW(A1)-1,48)+1
Copy B1 down to B96
This quickly numbers 2 cycles of 1-48, viz.:
1-48 in B1:B48, then 1-48 again in B2:B96

Then, in the sheet with your 48 horizontal "shifts" listed in B1:N48
(assume this sheet is named as: Z)

Insert a new row1, then put in the new A1:
=IF(ISNUMBER(MATCH(TODAY(),Y!A:A,0)),OFFSET(X!A:A, ,MATCH(VLOOKUP(TODAY(),Y!A:B,2,0),X!$1:$1,0)-1),"")
Copy A1 down to A49

A2:A49 returns the required roster from X
depending on the current date
(i.e. the Saturday's date which matches the current date)
[A1 returns the roster #]

And if the current date doesn't match with the Sat dates listed in Y's col A,
then col A will appear "blank".

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jinvictor" wrote:

what i want is:

a1-a48 different names
b1-n1 different shifts, etc: morning shift, afternoon shift,
night shift, rdo
b2-n2 different shifts again,different as b1-n1
all the way to line 48

every saturday fornight week, we work drop 1 line, say if my name in a1
this fornight, saturday 2 weeks later, my name will be on line 2(b2-n2).
and person in a48 will be in a1.
how can i do it so every 2nd saturday when i open the workbook, it
automaticaly change to new line which i suppose to be.

thanx


--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=548256

  #5   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default auto change line

Typo in line:
1-48 in B1:B48, then 1-48 again in B2:B96


should read as:
1-48 in B1:B48, then 1-48 again in B49:B96


--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change the auto filter tabs from blue to red? Daryl Bruce Setting up and Configuration of Excel 0 May 11th 06 01:11 PM
Can I change line numer or column name Dave Bonneville Excel Discussion (Misc queries) 2 November 1st 05 08:29 PM
Change the line of a chart. KinSjama Excel Discussion (Misc queries) 1 October 28th 05 04:36 PM
date auto change Colin2u Excel Discussion (Misc queries) 3 August 21st 05 05:26 AM
how do I change a line series to a column series in excel? Mati Charts and Charting in Excel 1 May 12th 05 09:32 AM


All times are GMT +1. The time now is 11:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"