| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing the data in a cell
I have a column of data, say column A. I want to show the value of every 5th
row in column A in column C. This means cell C1 will reference cell A1, cell C2 will reference cell A6, cell C3 will reference cell A11, etc. Is there a formula that I can drag down column C to produce this result? |
|
#2
|
|||
|
|||
|
Quote:
Paste this formula in cell B1 and copy down as far as needed. Code:
=IF(MOD(ROW()-1,5)=0,ROW(),"") Code:
=IF(ISERR(SMALL(B:B,ROW(A1))),"",OFFSET($A$1,SMALL(B:B,ROW(A1))-1,0)) Last edited by GoBow777 : January 12th 08 at 07:21 PM |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing the data in a cell
Try this in c1 and drag down
=INDEX(A:A,(ROW(A1)-1)*6+1) Mike "kraway" wrote: I have a column of data, say column A. I want to show the value of every 5th row in column A in column C. This means cell C1 will reference cell A1, cell C2 will reference cell A6, cell C3 will reference cell A11, etc. Is there a formula that I can drag down column C to produce this result? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing the data in a cell
OOps your step was 5 not 6 so I really meant this!!
=INDEX(A:A,(ROW(A1)-1)*5+1) Mike "kraway" wrote: I have a column of data, say column A. I want to show the value of every 5th row in column A in column C. This means cell C1 will reference cell A1, cell C2 will reference cell A6, cell C3 will reference cell A11, etc. Is there a formula that I can drag down column C to produce this result? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing the data in a cell
Hi,
=OFFSET($A$1,(ROW()-1)*5,0) HTH |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing the data in a cell
That didn't work but I think I have the idea now.
"Mike H" wrote: OOps your step was 5 not 6 so I really meant this!! =INDEX(A:A,(ROW(A1)-1)*5+1) Mike "kraway" wrote: I have a column of data, say column A. I want to show the value of every 5th row in column A in column C. This means cell C1 will reference cell A1, cell C2 will reference cell A6, cell C3 will reference cell A11, etc. Is there a formula that I can drag down column C to produce this result? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing the data in a cell
what didn't work about it?
"kraway" wrote: That didn't work but I think I have the idea now. "Mike H" wrote: OOps your step was 5 not 6 so I really meant this!! =INDEX(A:A,(ROW(A1)-1)*5+1) Mike "kraway" wrote: I have a column of data, say column A. I want to show the value of every 5th row in column A in column C. This means cell C1 will reference cell A1, cell C2 will reference cell A6, cell C3 will reference cell A11, etc. Is there a formula that I can drag down column C to produce this result? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing the data in a cell
Your formula works, I screwed it up when I applied to actual data. My fault
totally. Sorry:) "Mike H" wrote: what didn't work about it? "kraway" wrote: That didn't work but I think I have the idea now. "Mike H" wrote: OOps your step was 5 not 6 so I really meant this!! =INDEX(A:A,(ROW(A1)-1)*5+1) Mike "kraway" wrote: I have a column of data, say column A. I want to show the value of every 5th row in column A in column C. This means cell C1 will reference cell A1, cell C2 will reference cell A6, cell C3 will reference cell A11, etc. Is there a formula that I can drag down column C to produce this result? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing the data in a cell
No problem, thank you for the feedback
Mike "kraway" wrote: Your formula works, I screwed it up when I applied to actual data. My fault totally. Sorry:) "Mike H" wrote: what didn't work about it? "kraway" wrote: That didn't work but I think I have the idea now. "Mike H" wrote: OOps your step was 5 not 6 so I really meant this!! =INDEX(A:A,(ROW(A1)-1)*5+1) Mike "kraway" wrote: I have a column of data, say column A. I want to show the value of every 5th row in column A in column C. This means cell C1 will reference cell A1, cell C2 will reference cell A6, cell C3 will reference cell A11, etc. Is there a formula that I can drag down column C to produce this result? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data referencing | Excel Worksheet Functions | |||
| Referencing Data | Excel Worksheet Functions | |||
| referencing data from a table | Excel Worksheet Functions | |||
| Prevent equation from skipping data when referencing import data? | Excel Discussion (Misc queries) | |||
| referencing a sheet named in a cell then using data from that sheet | Excel Worksheet Functions | |||
Hybrid Mode
