Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the auto filter tabs from blue to red? | Setting up and Configuration of Excel | |||
Can I change line numer or column name | Excel Discussion (Misc queries) | |||
Change the line of a chart. | Excel Discussion (Misc queries) | |||
date auto change | Excel Discussion (Misc queries) | |||
how do I change a line series to a column series in excel? | Charts and Charting in Excel |