+ Reply to Thread
Results 1 to 3 of 3

Help indirect and mid formula referencing previous sheet

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    13

    Help indirect and mid formula referencing previous sheet

    Basically I have no idea how but i have the following formula which references a cell on a previous sheet


    =(INDIRECT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))-1&"!I5")

    When i copy the formula down i want the formula to bring me I6 rather than I5

    AND ALSO THE SAME FOR ACROSS I WANT IT TO BRING J5 NOT I5

    Is there anyway i can do this without having to overtype the formula in each cell?


    THANKS IN ADVANCE

    PS I AM A COMPLETE NOVICE AND HAVE VERY LITTLE EXPERIENCE WITH EXCEL SO PLEASE GO EASY ON ME!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Help indirect and mid formula referencing previous sheet

    Hi,

    Replace with:

    =INDIRECT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))-1&"!"&ADDRESS(5+ROWS($A$1:$A1)-1,9+COLUMNS($A$1:A$1)-1,4,1))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help indirect and mid formula referencing previous sheet

    Try

    =(INDIRECT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))-1&"!"&CELL("address",I5))

+ 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