Results 1 to 8 of 8

20050125, 12:26 #1
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Increment formula by 7 horizontally (Excel 2003)
A job downloads data to Excel nightly that I need to graph. It's in a format as shown on the Raw Data worksheet in the attached file. The Graphing Data worksheet needs to have the values from columns E and I on the Raw Data sheet. How can I create a formula on the Graphing Data sheet that will put the data from every 7th row on the Raw Data sheet?
Thanks in advance.

20050125, 12:43 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Increment formula by 7 horizontally (Excel 2003)
Try this formula in C4 on the Graphing Data sheet:
=OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)5,4)
You can fill it down and right. Similarly, use the following formula in C15:
=OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C15)16,8)
and fill down and right.

20050125, 12:43 #3
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Increment formula by 7 horizontally (Excel 2003)
<hr>How can I create a formula on the Graphing Data sheet that will put the data from every 7th row on the Raw Data sheet?
<hr>
Do you not mean pull the data? i don't understand!Regards,
Rudi

20050125, 12:53 #4
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Increment formula by 7 horizontally (Excel 2003)
Ahh...Hans clarified it!
You can also add if(iserror(.... to the formula Hans provided to erase the #N/A output of blanks. Try this...
In cell C4: =IF(ISERROR(OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)5,4)),"",OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)5,4))
In Cell C15: =IF(ISERROR(OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C15)16,8)),"",OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C15)16,8))Regards,
Rudi

20050125, 13:23 #5
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Increment formula by 7 horizontally (Excel 2003)
Terrific, thanks.
I had the right idea using the OFFSET and MATCH functions, just didn't get the syntax right.

20050202, 15:08 #6
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Increment formula by 7 horizontally (Excel 2003)
I really appreciate the response, but I'm afraid I still need some help. The formula works great, but I'll need to utilize the same type of offset/match formula, so I need to understand it. In this formula,
=OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)5,4)
I understand except for +Row(C4)5 Can someone please explain that to me? Thankx

20050202, 15:24 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Increment formula by 7 horizontally (Excel 2003)
=OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)5,4)
The part MATCH(C$3,'Raw Data'!$A:$A,0) looks for the value of C3 (the month) in column A on the Raw Data sheet, and returns the index number of where it was found, i.e. the row number in column A. For the first date, this is 1, for the second date, it is 8, etc.
From the cell where the date is found, we want to go 4 columns to the right (i.e. to column E), and 0 rows down for C4, 1 row down for C5, 2 rows down for C6 etc.
We need to add 1 to the index for C4 (remember, the index was 1), 0 for C5, 1 for C6 etc. In each case, we add 5 less than the row number of the cell containing the formula. This is accomplished by using +ROW(C4)5 (which will become +ROW(C5)5 in C5, and +ROW(C6)5 in C6 etc.)
Note: we could also have used ROW() instead of ROW(C4). If no argument is supplied, ROW() returns the row number of the cell containing the formula.

20050202, 15:54 #8
 Join Date
 Jan 2004
 Location
 Melbourne, Fl USA
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Increment formula by 7 horizontally (Excel 2003)
I get it now. Thank you so much for the explaination. This is going to make my life alot easier!