Forum Home
Press F1
Thread ID: 25928	2002-10-15 08:10:00	Xl query, copying data from every 8th row..	John W (523)	Press F1

Post ID	Timestamp	Content	User
89430	2002-10-15 08:10:00	I have a spreadsheet, that records daily income, expenditure, hours worked by staff, etc... all across the sheet on 1 row. Mon-Fri take a row each, every 6th row is a TOTAL of the 5 rows above, the 7th row is blank. This happens week after week... down the sheet. On sheet 2 in the same workbook, I Edit/Copy/Paste Row six (the Total row), I use the same process in the Total next row the following week. I use AutoFill to get the next (3, 4, 5) weeks Total, but here it all turns to Custard. Autofill will not jump to the 7th row in progression to give me the totals Im after. Any clues as to what Im doing wrong? Thanks..... John W Dunedin.	John W (523)
89431	2002-10-16 02:54:00	I don't think AutoFill has the nous to do what you're asking. I would personally use a macro. (Going from memory here so bear with me) - choose Tools, Macro... , Record New Macro - enter a name, click OK - copy and paste every 7th row for each week - click the stop button You could then run this macro whenever you wanted to copy the weekly totals. Previous weeks would still get re-copied, but I assume the figures for them wouldn't change.	antmannz (28)
89432	2002-10-16 05:00:00	Thanks, I had thought of that, but hoped there might have been an easier fix... Cheers....John.	John W (523)
89433	2002-10-16 07:31:00	Sorry dont know an answer John but Fill Down-Across tends to have difficulties when there are spaces in between. I cant seem to get autofill to work very well when referencing other sheets either so you seem to be getting farther than I can. Using a macro is good for repetitive tasks like this but are you just setting up sheet2 or do you have to copy every total row again and again? A great site for asking Excel questions is Mr Excel. Perhaps ask your question there and they may have an answer for you.	parry (27)
89434	2002-10-16 07:55:00	I composed a reply today but it never appeared :-( You don't need macros, as the OFFSET function will do as required without having to copy any data from Sheet1 to Sheet2. But you do need to add a column of numbers from 1 downwards to make it work in the following quick and dirty solution. This column of numbers can be on Sheet3 from A2 downwards 1,2,3,4 etc I have assumed that the first total row is at A6 on Sheet1 therefore the second total row and subsequent rows will be 7 rows down ie A13, A20 etc. Further assuming that the first total row from Sheet1 is at Sheet2 A1 and total row Sheet1 A13 is at Sheet2 A2. In Sheet2 cell A1 enter the formula =Sheet1!A6 Copy this formula across to however many columns are required. In Sheet2 cell A2 enter the formula =OFFSET(Sheet1!A$6,7*Sheet3!$A2,0) - the position of the $ signs is very important. Copy this formula across for however many columns are required, then copy the selection down past where it picks up all your current data from Sheet1. Sheet2 will be instantly updated with information from every 7th row from Sheet1 without having to copy any data over. The OFFSET formula is taking data Offset from row A6 in multiples of 7 rows by the column multiplyer on Sheet3. This works in XL2000. HTH	Russell D (18)
89435	2002-10-16 08:11:00	Thats neat Russell, Ill try that out over the next few days. Ive never had anything to do with offset in the past, Ill look it up in my XL Macro reference manual. Im a little confused as to where Sheet 3 come in. Ill assume it means Sheet 2. Seeing as you are familiar with XL, here is another little problem, concerning the same sheet. In Column A, every row is given a Date, eg 15/10/02, but the equivalent of Sat - where the total row is located & Sunday which is blank, neither have the date entetred. What macro can I use to get the active Cell to go to the current date? The only problem with all of this, is that I write my Macros in XL4, (so easy to read and no verbose or poorly coloured text). Cheers.....John.	John W (523)
89436	2002-10-16 10:29:00	Sheet3 is used soley for the column of multiplyer numbers to make the Offset work in the example given. This column of numbers 1,2,3,4,5 etc can be anywhere provided the formula points to it. It's a bit late - I will have to think on the date question.	Russell D (18)
89437	2002-10-16 20:56:00	The date issue is unclear- If you want the active cell to contain the current date use Activecell.value = Date If you want to Find the current date which is located in column A use On Error GoTo msg Dim cd cd = Date Columns("A:A").Find(cd).Activate Exit Sub msg: MsgBox ("Current date not listed, or in the incorrect format") HTH	Russell D (18)
89438	2002-10-17 10:07:00	A solution to the Sheet3 or sheet2 column of numbers - a work around this is to substitute 7*Sheet3!$A2 with 7*Row($A1) - then you don't need a separate column of figures at all - ya gotta love Excel when you know how to make it work for you!	Russell D (18)
1