Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Dec 2009
    Location
    Islamabad
    Posts
    26
    Thanks
    7
    Thanked 1 Time in 1 Post
    Hi all

    Is it possible to refer to a sheet within a same workbook with its position instead of its name? I mean in formula only (no VB)

    Or any other solution of my problem, see below.

    The problem with me is that many formulas on each sheet refers to its previous sheet, and I have to use a workaround where I manually write name of previous sheet in a cell and then construct these formulas using INDIRECT function.

    Isn't it good to automate this process (without VB please)?

    TIA
    [hr]
    Kamran (کامران)
    Islamabad, Pakistan

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Muhammad,

    Sorry, but I couldn't find a way to do this with out VBA. So here's a very minimal VBA solution using a UDF {user defined function} you can enter then use just like an Excel builtin function. Please note that it will reference sheets based on the order in which they are added to the workbook {see graphic below}, so if you add sheets they should always be moved to the end (right side) for the visual position to match the relative position!!!
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Dec 2009
    Location
    Islamabad
    Posts
    26
    Thanks
    7
    Thanked 1 Time in 1 Post
    Thanks RetiredGeek [ although geek never retires in his lifetime ]

    I think I haven't clearly stated my problem. And my requirement yield to an elegent solution, atleast as far as I am concerned.

    I have seperate sheets in a work-book for each month of an year. And every month (except January) have some carry-forward values. So after your VB-solution I spent a couple of hours and come up with this:

    Sheets in the workbook has names like: "May, 2010" and "June, 2010" for example.

    I used following formula to come up with name of previous month sheet:

    =TEXT(VLOOKUP(MID(CELL("filename",A1),1+FIND("]",CELL("filename",A1),1),3),PrvMonLkup,2,FALSE),"m mmm, yyyy")

    where PrvMonthLkup is a lookup table for all months. see attached file for understanding. Hope this can help others also.

    Thanks anyway

    Kamran, Islamabad, Pakistan
    Attached Files Attached Files
    [hr]
    Kamran (کامران)
    Islamabad, Pakistan

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by Muhammad Kamran Shakil View Post
    Thanks RetiredGeek [ although geek never retires in his lifetime ]
    We just get older ... and Geekier

    have you tried playing with the get.document and get.workbook functions?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Retired Geek,

    I am using Excel 2K.

    I loved your Relative Sheet Reference UDF as soon as I saw it!

    I have tried to implement it but am having problems in that whenever I go to another sheet, and some recalc occurs, I lose the plot on the sheet which uses your UDF.

    What I mean is that I get the Relative References changed to what they were from the sheet where the recalc occurred!

    I suspect this is somehow because of the reference to "ActiveSheet" in the code.

    I notice this does not seem to happen in your sheet as I have downloaded it.

    After initially putting the code in Personal.xls, I have now moved it to the workbook I am using and deleted it from Personal.xls.

    Would you have any thoughts as to what may be my problem?

    Regards,

    Peter Moran

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Peter,

    Any chance you could post a copy of your workbook so I can see it in action?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You should never use Activesheet (or activecell etc) in a UDF. You also need to make the UDF volatile so that it recalculates whenever the workbook does:

    Code:
    Public Function RelSheet(iPos As Integer, zRange As String) As Variant
    
        Dim shtActive As Worksheet
        Application.Volatile True
        Set shtActive = Application.Caller.Worksheet
        On Error GoTo BadSheetReference
        RelSheet = Sheets(shtActive.Index + iPos).Range(zRange).Value
        
        GoTo ExitFunction
        
    BadSheetReference:
    
        RelSheet = "#Error"
        
    ExitFunction:
    
    End Function

    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Thanks for your update - no complaints now - all is working as it should!

    And thanks to RetiredGeek for his input.

    Also I found this interesting post on the Ozgrid site:
    http://www.ozgrid.com/forum/showthread.php?t=24994

    Thanks again guys,

    Peter Moran

Posting Permissions

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