+ Reply to Thread
Results 1 to 8 of 8

Using MOD and ROW functions

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    93

    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
    Last edited by H28Sailor; 08-10-2013 at 06:27 AM. Reason: mis-spelling

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using MOD and ROW functions

    =Sheet1!C0 ?

    =INDEX(Sheet1!A:A,ROWS($A$1:A2)*6-6) will give rows 6,12,18........
    Last edited by martindwilson; 08-10-2013 at 06:45 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    93

    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?

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,569

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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))
    Attached Files Attached Files
    Last edited by martindwilson; 08-10-2013 at 08:10 AM.

  6. #6
    Registered User
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    93

    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 1 - headings
    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

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using MOD and ROW functions

    no its not! sheet 2 shows the rows its returning are you looking at the right thing?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Using MOD and ROW functions

    Yes - you are correct

    Apologies for not getting it right........

    Many thanks

    Bob M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Which Excel functions work in user-defined functions ?
    By RogeratCCCC in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-28-2012, 06:47 PM
  2. Replies: 1
    Last Post: 02-10-2012, 05:27 PM
  3. Replies: 0
    Last Post: 11-15-2007, 05:24 AM
  4. [SOLVED] efficiency: database functions vs. math functions vs. array formula
    By nickname in forum Excel General
    Replies: 2
    Last Post: 07-13-2006, 11:30 PM
  5. Replies: 0
    Last Post: 06-05-2006, 05:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1