+ Reply to Thread
Results 1 to 6 of 6

Autofill problem

  1. #1
    Registered User
    Join Date
    12-11-2008
    Location
    Perth
    Posts
    11

    Autofill problem

    I have a really basic question, but can't seem to find the answer.

    I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function.

    Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
    D24, instead of C25

    Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?

    Thanks

    Lesa
    Last edited by Lesa Muir; 12-11-2008 at 09:38 PM. Reason: Solved

  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
    In what cell does the formula first appear?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-11-2008
    Location
    Perth
    Posts
    11
    D17

    I know I can transpose all of the data to be in a column instead of a row, but that would mean totally changing someone's model, I would rather just use a function to do it in the formula if available?

    Thanks

  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
    Change the formula in C17 to =C17 + INDEX(Sheet2!$C$24:$IV$24, ROW() - 16)

    Make the C17 part row-absolute (i.e., C$17) if you don't want it to change when the formula is dragged down.

  5. #5
    Registered User
    Join Date
    12-11-2008
    Location
    Perth
    Posts
    11
    Great thanks, can you just advise what the -16 tells Excel to do, just so I understand.

    Thanks

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    The formula indexes across the row; the 16 is there so when the formula is in row 17, it indexes the first value in the range.

    If you're good with that solution, would you please mark the thread as solved?


    To mark a thread as solved:

    Click the Edit button on your first post in the thread

    Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

    If more than two days have elapsed since your first post, ask a moderator to mark it.

+ 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