+ Reply to Thread
Results 1 to 6 of 6

Stop formula changing cell reference but change sheet number when dragged?

  1. #1
    Registered User
    Join Date
    01-26-2010
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    23

    Stop formula changing cell reference but change sheet number when dragged?

    Im Sure it is really simple but can't figure it out.

    What do i need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?

    Please Login or Register  to view this content.
    Last edited by tmtjamie; 02-08-2010 at 03:22 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Stop formula changing cell reference but change sheet number when dragged?

    =INDIRECT("'"Sheet"&ROWS($A$1:$A1)&"'!D6")

    copied down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-26-2010
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Stop formula changing cell reference but change sheet number when dragged?

    Thanks For Replying NBVC

    Do i need to change anything on this code as the formula isn't working for me?

    Sorry if it is obvious

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Stop formula changing cell reference but change sheet number when dragged?

    Yes, I added one to many quotes:

    Try: =INDIRECT("'Sheet"&ROWS($A$1:$A1)&"'!D6")

  5. #5
    Registered User
    Join Date
    01-26-2010
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Stop formula changing cell reference but change sheet number when dragged?

    Perfect!!

    Thank You

  6. #6
    Registered User
    Join Date
    12-15-2015
    Location
    Regina, Saskatchewan, Canada
    MS-Off Ver
    2013
    Posts
    22

    Re: Stop formula changing cell reference but change sheet number when dragged?

    How would I use this same formula but to copy across? I have a spreadsheet that has Months in the tabs and a summary sheet. Each month of information is exactly the same so for instance in cell N820 I would want from Month1 cell D403's information. M820 I would want Month2 cell D403's information and so on, up to Month13. Any help would be fantastic.

    Thank you.

+ 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