Results 1 to 4 of 4

Thread: Automating an Equation

  1. #1
    Seeker John Davis's Avatar
    Join Date
    Sep 2011
    Location
    Florida
    Posts
    8
    Articles
    0

    Question Automating an Equation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good afternoon all,
    I have an equation that takes a row of 18 cells and puts it into a column of 18 cells. No problem there. But what I need to know is how to use the equation for the next set of 18 cells right below the first. This is my equation =INDEX($B$1:$S$1,ROWS(A$1:A1)). I put this in A1 and auto fill drag down to row 18.

    How do i repeat this step for my next 18 rows (that have different information that the top row)? A small example below. So after I auto fill here from A1 to A3, how can I alter the original equation to keep going and make r s and t appear in A4:6? Is there a way to +18 to all the numerals in the formula so it will keep adding 18 (in the example +3) for every next set? But still be able to auto fill (A2, A3 in the example)?

    A B C D
    1 x x y z
    2 y
    3 z
    4 ? r s t
    5 ?
    6 ?

    Thanks!

    PS I attached the top portion of the file in question to this thread.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Hi John, and welcome to the forum,

    It's late here, and I'm a little tired, but if you're just trying to copy the block of formulas down, you could do a Find/Replace on your existing selection to replace $1 with 1. At that point if you copy the block from Rows 1:18 down to 19:36, it would apply the correct formulas.

    I'll try to look at this again to solve your original question late this weekend.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Try this

    =INDEX($B$1:$S1,(INT((ROW()-1)/18)*18)+1,IF(MOD(ROW(),18)=0,18,MOD(ROW(),18)))

  4. #4
    Seeker John Davis's Avatar
    Join Date
    Sep 2011
    Location
    Florida
    Posts
    8
    Articles
    0

    Thanks!

    This worked great, thanks for the help. You guys seriously saved me over 100 hours of work for this project I'm working on. Much appreciated.

Posting Permissions

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