Formula to reference every second row

[Neville Bailey]

I have created a formula to reference a cell in another workbook. I now want
to copy the formula down a number of consecutive rows, but I want it to
reference the cell in every second row in the other workbook.

How can I do that?

 

[Mike H]

Well you don't tell us the formula and whetjer its odd or even rows so heres
a solution for even rows provided there is not text in the range

=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100))

Change the xero to 1 for odd rows.

If there is or could be text in the range use this instead
=SUM(IF(MOD(ROW($A$1:$A$100),2)=0,$A$1:$A$100,0))
This is an array so enter with CTRL+Shift+Enter.

Mike

 

[MartinW]

Hi Neville,

You dont say what your formula is but this may be of some help.
This formula placed in Sheet2 and dragged down will return
every second value from column A in Sheet1.
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)

HTH
Martin

 

[Neville Bailey]

Hi Neville,

You dont say what your formula is but this may be of some help.
This formula placed in Sheet2 and dragged down will return
every second value from column A in Sheet1.
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)

HTH
Martin





Brilliant! Thanks MartinW, it works like a charm!

 

[MartinW]

Glad it worked, thanks for posting back.

Regards
Martin