| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing every 5th cell in another sheet
I have one sheet (Sheet1) that contains data. Everey 5th row
in this sheet I want to put a referce to in sheet2. E.g. Sheet2.A1 = Sheet1! A1 Sheet2!A2 = Sheet1! A6 Sheet2!A3 = Sheet1! A11 Sheet2!A4 = Sheet1! A16 and so on... Is there a way I can use Autofill in Sheet2 and return every 5th cell from the other sheet, sheet1? Regards Jan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing every 5th cell in another sheet
No easy way unfortunately but this formula should do the trick:
=INDIRECT("Sheet1!A"&(ROW()*5)-4) -- Rgds, Geoff " wrote: I have one sheet (Sheet1) that contains data. Everey 5th row in this sheet I want to put a referce to in sheet2. E.g. Sheet2.A1 = Sheet1! A1 Sheet2!A2 = Sheet1! A6 Sheet2!A3 = Sheet1! A11 Sheet2!A4 = Sheet1! A16 and so on... Is there a way I can use Autofill in Sheet2 and return every 5th cell from the other sheet, sheet1? Regards Jan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing every 5th cell in another sheet
Hi Jan,
One way, post this in Sheet2 cell A1 =INDEX(Sheet1!A:A,ROW()*5-4) Grab the fill handle and drag down as far as is needed HTH Martin wrote in message ups.com... I have one sheet (Sheet1) that contains data. Everey 5th row in this sheet I want to put a referce to in sheet2. E.g. Sheet2.A1 = Sheet1! A1 Sheet2!A2 = Sheet1! A6 Sheet2!A3 = Sheet1! A11 Sheet2!A4 = Sheet1! A16 and so on... Is there a way I can use Autofill in Sheet2 and return every 5th cell from the other sheet, sheet1? Regards Jan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing every 5th cell in another sheet
On 20 Jun, 12:26, xlbo wrote:
No easy way unfortunately but this formula should do the trick: =INDIRECT("Sheet1!A"&(ROW()*5)-4) -- Rgds, Geoff " wrote: I have one sheet (Sheet1) that contains data. Everey 5th row in this sheet I want to put a referce to in sheet2. E.g. Sheet2.A1 = Sheet1! A1 Sheet2!A2 = Sheet1! A6 Sheet2!A3 = Sheet1! A11 Sheet2!A4 = Sheet1! A16 and so on... Is there a way I can use Autofill in Sheet2 and return every 5th cell from the other sheet, sheet1? Regards Jan- Skjul sitert tekst - - Vis sitert tekst - Sweet! Very Nice. Thanks a lot! Regards Jan. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing every 5th cell in another sheet
Try this:
=OFFSET(Sheet1!$A$1,(ROWS($1:1)-1)*5,0) " wrote: I have one sheet (Sheet1) that contains data. Everey 5th row in this sheet I want to put a referce to in sheet2. E.g. Sheet2.A1 = Sheet1! A1 Sheet2!A2 = Sheet1! A6 Sheet2!A3 = Sheet1! A11 Sheet2!A4 = Sheet1! A16 and so on... Is there a way I can use Autofill in Sheet2 and return every 5th cell from the other sheet, sheet1? Regards Jan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| referencing to a sheet that it`s name is stored on a cell | Excel Worksheet Functions | |||
| odd behavior when referencing a cell on another sheet | Excel Discussion (Misc queries) | |||
| Referencing cell in another sheet yields null? | Excel Worksheet Functions | |||
| referencing Excel sheet name in cell | Excel Worksheet Functions | |||
| referencing a sheet named in a cell then using data from that sheet | Excel Worksheet Functions | |||
Linear Mode
