I have a spreedsheet which we enter data in on a daily basis. At the end of
the day I want the last data to go to next sheet. Now I know how to do this
but is there an easier way by copiny and pasting instead of doing it manual.
example
On day 2 I collect the data from day 1, then on day 3 I get info from day 2
etc till the end of month. what is the quickest way to do this. The way I am
doing it by entering in the formula by ="1st'A1 etc. I know to click on the
cell and the info goes in but I have 31 days to do and it is time consuming.
thanks in advance

One play using non-array formulas ..

Link to demo file at: http://cjoint.com/?kmfnGKCeap
AutoMovingData_ToSheets_v1_Scott45_wksht.xls

Assuming source data (sample below)
is in sheet: Data, cols A to C, data from row2 down

Day Item Qty
1 G 83
1 C 13
1 K 17
1 B 27
2 L 55
2 P 22
etc

In sheet: Data
---------------------
List the days in col E, in E2 down: 1,2,3,4,5 ...

Put in F2:
=IF($A2=INDIRECT("E"&COLUMNS($A$1:A1)+1),ROW(),"")

Copy F2 across as many cols as there are days listed in col E,
then fill down to say, row200 to cover the max expected data range
in cols A to C. Leave the top row cells (F1,G1,H1,etc) empty.

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan Grove.

In a new sheet named: 1 (for day 1)
-----------------------
Paste the col headers into A1:B1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Data!$E:$E,,
MATCH(WSN+0,Data!$E:$E,0)-1),ROWS($A$1:A1))),"",
INDEX(Data!B:B,MATCH(SMALL(OFFSET(Data!$E:$E,,
MATCH(WSN+0,Data!$E:$E,0)-1),ROWS($A$1:A1)),
OFFSET(Data!$E:$E,,MATCH(WSN+0,Data!$E:$E,0)-1),0)))

Copy A2 across to B2, fill dow to B200
(cover the same range size as was done in "Data" cols F to J)

Cols A & B will return only the lines for day: 1
from "Data", all neatly bunched at the top, viz.:

Item Qty
G 83
C 13
K 17
B 27

Now, just make a copy of the sheet: 1, rename it as: 2
and you'd get the corresponding results for day: 2

Repeat the copy rename sheet process
to get the rest of the day sheets (a one time job)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"scott45" wrote in message
...
I have a spreedsheet which we enter data in on a daily basis. At the end
of
the day I want the last data to go to next sheet. Now I know how to do
this
but is there an easier way by copiny and pasting instead of doing it
manual.
example
On day 2 I collect the data from day 1, then on day 3 I get info from day
2
etc till the end of month. what is the quickest way to do this. The way I
am
doing it by entering in the formula by ="1st'A1 etc. I know to click on
the
cell and the info goes in but I have 31 days to do and it is time
consuming.
thanks in advance

Another link to the sample:
http://www.savefile.com/files/8416106
AutoMovingData_ToSheets_v1_Scott45_wksht.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--