# Using MOD and ROW functions

1. ## Using MOD and ROW functions

Hi
I have a workbook with two worksheets: Sheet 1 and Sheet2
Sheet 1 has date, Time, Value1, Value2

In Sheet 2:- I have the following cells with formulae.............

A1 = Sheet1!A6
B1 = Sheet1!B6
C1=Sheet1!C0
D1 = Sheet1!D6

A2 = Sheet1!A12
B2 = Sheet1!B12
C2=Sheet1!C6
D2 = Sheet1!D12

etc.

How do I create a formulae using both the MOD function and the ROW function so that I can Copy Row 1 and Paste to Rows2, 3, 4, 5, etc where the result is as shown above?

Many thanks

Bob M  Register To Reply

2. ## Re: Using MOD and ROW functions

=Sheet1!C0 ?

=INDEX(Sheet1!A:A,ROWS(\$A\$1:A2)*6-6) will give rows 6,12,18........  Register To Reply

3. ## Re: Using MOD and ROW functions

Sorry....my example was incorrect

I do want rows 8, 14, 20, 26 etc for columns A, B, and D but for the C column I want the value from row (8-6), (14-6), (20-6), (26-6) etc

Thanks

p.s. when I put the above formula in my screen goes of to my Docs Library?  Register To Reply

4. ## Re: Using MOD and ROW functions

"Sorry....my example was incorrect"

So could you clarify what your current formulas are in Sheet2 A1:D1?

Presume they're:

A1: =Sheet1!A8
B1: =Sheet1!B8
C1: =Sheet1!C2
D1: =Sheet1!D8

Regards  Register To Reply

5. ## Re: Using MOD and ROW functions

here you go
mind you ,you could use 1 draggable formula
=INDEX(Sheet1!A:A,ROWS(\$A\$1:\$A2)*6-IF(MOD(COLUMN(A\$1),4)=3,10,4))  Register To Reply

6. ## Re: Using MOD and ROW functions

Hi there

Thank you for the formulae....................

But the result is every row from sheet 1 !

I am seeking on sheet 2
Row 2) - Sheet 1 [A8], Sheet 1 [B8], Sheet 1 [C2] and Sheet 1 {D8]
Row 3) - Sheet 1 {A14], Sheet 1[B14], Sheet 1[C8] and Sheet 1 [D14] etc.

Many thanks

Bob M  Register To Reply

7. ## Re: Using MOD and ROW functions

no its not! sheet 2 shows the rows its returning are you looking at the right thing?  Register To Reply

8. ## Re: Using MOD and ROW functions

Yes - you are correct Apologies for not getting it right........

Many thanks

Bob M  Register To Reply