+ Reply to Thread
Results 1 to 6 of 6

Repeat formula

  1. #1
    Registered User
    Join Date
    08-26-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    27

    Repeat formula

    Hi there,

    In cell DG8 I have the following formula

    ="vs "&AG1&" "&TEXT(AG3,"dd/mm/yy")

    This works fine. In cell DI8 (two cells along) I would like to create a formula which does the same thing as above but instead of using AG1 it obtains its data from cell AY1 (18 cells along).

    I would then like to repeat this formula another 2 cells along, and then again and so on. without doing it manually is there a formula that is able to read 2 cells back and then add 18 cells along to where that cell gets its data from?

    Any suggestions?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Repeat formula

    I suggest you post a workbook and show an example of what you want to do.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-26-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Repeat formula

    I have set up a dummy worksheet to make the concept simpler to understand.

    I would like
    E18 to equal E5
    G18 to equal H5
    I18 to equal K5

    But I don't want to enter this formula manually as this would take a long time if I had a large data set.

    So instead I would like E18 to use C18 to work out what is in E5 by searching 3 cells along from B8. Is this possible?
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Repeat formula

    Sorry, that's not enough of an example for me to understand what you're after.

    Perhaps someone else does.

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

    Re: Repeat formula

    clear as mud that !!!!
    "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

  6. #6
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: Repeat formula

    I understand the question but not sure of the solution. To reword...

    He wants a formula, that when copied horizontally, uses a relative reference that changes by a different number of columns. So if he copies the formula two columns over, he'd like the relative reference to adjust by three columns. The adjustment would have to cummulative as the formula is copied further across the sheet.

    My thought for a solution would be to add key values at the top of each place and use an HLOOKUP. But maybe someone can figure out how to do it using OFFSETs.

    Edit: I did better w/ it than I expected... C18: =OFFSET($A$5,,COLUMNS($B18:C18)/2*3-2)
    For an 18 column jump, replace /2*3-2 with /2*18-17
    Last edited by masteff; 08-31-2009 at 06:57 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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