Using fixed offset in formula help

[John Schroeder]

I am trying basically to refer to separate worksheet with an initial cell reference and add a fixed 20 rows in a cell reference ( ie data is repeated every 20 rows in a column) for a fairly large array of data. This would allow me to better summarize the totals on the new worksheet. Was trying to avoid having to edit each cell. I looked at the array and indirect functions, but they dont seem to get me what I want. This seems to basic and kinda have a block how to get it.

 

[Andrew Poulsom]

Example for rows 1, 21, 41 etc:

=INDEX(Sheet2!A:A,((ROW(A1)-ROW(A$1))*20)+1)

 

[Jonmo1]

Welcome to the board...

Try

=INDEX('Other Sheet'!A:A,(ROWS(A$1:A1)-1)*20+1)

DO NOT CHANGE ROWS(A$1:A1)-1 (this has nothing to do with your data)

'Other Sheet'!A:A is the column to reference data from.
The 20 represents the inteveral (every 20 rows)
the +1 represents the FIRST row to reference.


Hope that helps.

 

[John Schroeder]

Thanks for quick response. Have a small problem. I modified the formula and the cell is looking for a "update values" and then returns a #REF. So I understand correctly, the !A:A for a cell ref in the "other sheet" for cell B7 would be !B:B and the last number would be a 7 in the formula?

 

[Andrew Poulsom]

You need to chage Other Sheet to the name of your worksheet. A:A means the entire column A. If you want B1:B7 put that.

 

[Jonmo1]

Right, it should be

=INDEX('Other Sheet'!B:B,(ROWS(A$1:A1)-1)*20+7)

 

[John Schroeder]

<TABLE style="WIDTH: 251pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=334 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 251pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2876909 class=xl87 height=17 width=334 colSpan=5 x:str="'=INDEX(wacog!A:A,(ROWS(A$1:A1)-1)*20+37)">=INDEX(wacog!A:A,(ROWS(A$1:A1)-1)*20+37)</TD></TR></TBODY></TABLE>

Still NG. I was looking in column A for the 37th cell and every 20 there after in Column A (see formula above) in worksheet "wacog". As soon as I enter the cell a dialog box shows up with the title "update values:wacog". I cancel it and #ref is then displayed.

 

[John Schroeder]

Got it. the worksheet had an extra character (space at end) which was not evident. Thank you both for your help. I am good to go.