+ Reply to Thread
Results 1 to 5 of 5

Formula for copying a sequence for every 8th cell

  1. #1
    JBSAND1001
    Guest

    Formula for copying a sequence for every 8th cell

    Hello,
    I am looking for a formula that will copy data either text or
    numerical data in a sequence but for every 8th cell. I keep running into the
    problem of excel using the abosulte/relative reference to capture the string.
    For example

    Cell A1:A20 = 1-20 I want to capture in sequence 1-20 in column B except
    every 8th cell.

    A1=1 B1=1
    A2=2 B2=""
    A3=3.... B3="".....
    A20=20 B8=2
    B9=""
    B10=""....
    B16=3

  2. #2
    Frank Kabel
    Guest

    Re: Formula for copying a sequence for every 8th cell

    Hi
    in B1 enter
    =IF(MOD(ROW()-1,8)=0,OFFSET($A$1,INT((ROW()-1)/8),0),"")
    and copy down.

    NOTE: every 8th cell means the sequence:
    B1
    B9
    B17
    ....

    and NOT
    B1
    B8
    B16

    --
    Regards
    Frank Kabel
    Frankfurt, Germany

    JBSAND1001 wrote:
    > Hello,
    > I am looking for a formula that will copy data either text or
    > numerical data in a sequence but for every 8th cell. I keep running
    > into the problem of excel using the abosulte/relative reference to
    > capture the string. For example
    >
    > Cell A1:A20 = 1-20 I want to capture in sequence 1-20 in column B
    > except every 8th cell.
    >
    > A1=1 B1=1
    > A2=2 B2=""
    > A3=3.... B3="".....
    > A20=20 B8=2
    > B9=""
    > B10=""....
    > B16=3




  3. #3
    Andy Brown
    Guest

    Re: Formula for copying a sequence for every 8th cell

    > A1=1 B1=1
    > A2=2 B2=""
    > A3=3.... B3="".....
    > A20=20 B8=2
    > B9=""
    > B10=""....
    > B16=3


    Strictly speaking that's not every 8th row. That aside, try something like

    =IF(ROW()=1,$A$1,IF(MOD(ROW(),8)<>0,"",INDIRECT("A"&(ROW()/8)+1)))

    HTH,
    Andy



  4. #4
    Aladin Akyurek
    Guest

    Re: Formula for copying a sequence for every 8th cell

    =INDEX($B$1:$B$100,(ROW()-ROW($A$1))*8+1)

    JBSAND1001 wrote:
    > Hello,
    > I am looking for a formula that will copy data either text or
    > numerical data in a sequence but for every 8th cell. I keep running into the
    > problem of excel using the abosulte/relative reference to capture the string.
    > For example
    >
    > Cell A1:A20 = 1-20 I want to capture in sequence 1-20 in column B except
    > every 8th cell.
    >
    > A1=1 B1=1
    > A2=2 B2=""
    > A3=3.... B3="".....
    > A20=20 B8=2
    > B9=""
    > B10=""....
    > B16=3


  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Hi,

    I had to add another one ... just for fun:
    B1: =IF(MOD(ROW()-1;8)=0;INDIRECT(ADDRESS(ROW()/8+1;1));"")

    Ola


    or
    =IF(MOD(ROW();8)=0;INDIRECT(ADDRESS(ROW()/8;1));"")
    ...depending on what you had in mind.
    Last edited by olasa; 01-02-2005 at 07:00 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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