Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    I posted a similar question about referencing sheets with VBA, but now I'm interested in just formulas.

    I have a spreadsheet (Summary.xls) which links to another spreadsheet (CoreData.xls).

    CoreData.xls has 2 worksheets. During September the 1st sheet will be labeled JUNE & the 2nd SEPTEMBER. When we get to December, the 1st sheet gets re-labelled SEPTEMBER & the 2nd DECEMBER.

    I want the SUMMARY file to always link to the 2nd worksheet & I can't use range names.

    Any ideas?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    John, I'm not sure I understand how and when the CoreData workbook sheets get updated, but the formula below will get the data from cell A1 in the CoreData worksheet which is named the same as the current month. If the current month is not what you want, then use the EOMonth function in the Analysis toolpak (Tools > Add-Ins menu) with the appropriate offset around the Now function. HTH --Sam<pre>=INDIRECT("[CoreData.xls]" & TEXT(NOW(),"Mmmm") & "!$A$1")</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    That worked a treat - never used INDIRECT before.

    I had to change it to: =INDIRECT("[CoreData.xls]" & A2 & "!$A$1")

    where A2 contains '0109' (YYMM format) which is the label on my CoreData spreadsheet. So I just need to change that cell everytime

    Thanks Sammy

  4. #4
    hess0147
    Guest

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    I have a similar issue...
    The INDIRECT function only works for remotely referemced workbooks when those workbooks are open. Does anyone know about a 3rd party add-in function that works like INDIRECT but also for links to closed workbooks? How about a work-around? MS tech support could not help.
    Mark

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    Oh Oh!!

    Hadn't noticed that indirect only works with the link file open. Although it doesn't chnage the answer to #REF# once the source file has been closed????

  6. #6
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    This should work regardless of whether the referenced workbook is open:

    ='C:WindowsDesktop[test data.xls]Sheet1'!A1

    You have to be careful to get the ' ' and [ ] in the right place - I always end up having to look it up (John Walkenbach's Excel 2000 Bible, which I wholeheartedly recommend).

    HTH

    Graeme

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    Still not quite there - any advances?

    Definately need to be able to update the links without the source files being open - there's far too many of them.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    Graeme's formula uses the "code" name for the first sheet added to the workbook, Sheet1 (if you right click on the sheet tab & view code, you will see the tab name & the code name: you can use either in a formula). However, from your posts, I think that you are always adding sheets, so Sheet1 maybe correct for a month, but then it will be incorrect. I think that you will have to open the workbook in VBA and use Sheets(2).Cells(1, 1) to access A1 on the second sheet (the collection indicies follow the tab order). Hope this finally gets you somewhere. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    The supplied Microsoft Excel Add-In "Update Add-in Links" does this ... umm, doesn't it? (I don't use it.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    I looked at the VBA code for the sheet and it is SHEET2 (0109). I've tried referencing this as part of a formula but don't seem to be getting it quite right (It's Friday!).

    To clarify my spreadsheet. There are 3 sheets, the 1st 2 are labeled in YYMM format. They are:
    0106
    0109
    Reconcile

    My formula should ALWAYS link to the 2nd sheet. The next time I have to work on this file, i'll have to rename the tabs to:
    0109
    0112
    Reconcile.

    As my formula stands, it will look for the tab '0109', which is now incorrect.

    Hope you get the picture.

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    You should be able to reference the sheet with either 0109 or SHEET2 unless you have another sheet with a tab-name of SHEET2. However, for the next month (the 0109, 0112, Reconcile configuration), you have probably created a new sheet for the 0112 tab, so it will have a code name of SHEET4 or greater and a formula that uses SHEET2 will still get the stuff from 0109. I would just write a macro to update the link each time the spreadsheet is opened. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    > You should be able to reference the sheet with either 0109 or SHEET2

    Well, I misspoke, although I thought that I tested it out. It now appears to me that you can only use the code name in VBA. Sorry for the wrong information.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    This time I tested it. Here's what I would do:
    <pre>Option Explicit
    Sub Auto_Open()
    Dim strThisBookName As String
    Dim wbCoreData As Workbook
    Dim strSheet2 As String
    strThisBookName = ActiveWorkbook.Name
    Set wbCoreData = Workbooks.Open(Filename:="CoreData.xls")
    strSheet2 = wbCoreData.Sheets(2).Name
    wbCoreData.Close
    Windows(strThisBookName).Activate
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "='[CoreData.xls]" & strSheet2 & "'!R1C1"
    End Sub
    </pre>

    You will need to add the directories to CoreData in both places. Just record a macro of opening CoreData and placing a reference to it in your workbook, then add the stuff that I wrote. HTFH (Hope this finally! helps) --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    Sam Barrett has written a solution in the direction I think you wanted, but I also realized that there is a MUCH simpler way to handle this, if acceptable to you:

    [Edited - hit the "Post it" button a little early!]

    Create a new sheet (I'll call it "Reference") in your source WB Coredata.xls
    On "Reference" have formulas that are nothing more than ='0109'!<celladdress>
    Reset your Summary.xls WB so it reads the data from the Reference sheet, i.e., ='[CoreData]Reference'!<celladdress>
    Changing the name(s) of 0109 and 1112 now will not affect the cells read by Summary.xls.
    Hide sheet Reference in WB Coredata.xls if you want to keep the appearance of Coredata.xls clean
    Use "Update Add-in Links..." to refresh Summary.xls without opening CoreData.xls
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

    > Create a new sheet (I'll call it "Reference") in your source WB Coredata.xls
    > On "Reference" have formulas that are nothing more than ='0109'!<celladdress>
    > Reset your Summary.xls WB so it reads the data from the Reference sheet, i.e., ='[CoreData]Reference'!<celladdress>

    > Changing the name(s) of 0109 and 1112 now will not affect the cells read by Summary.xls.

    But each month he adds a new sheet to CoreData, so at that time he would have to do a find and replace of all the formulas on the References sheet.

    However, if he can make a formula to compute the name of the second sheet based on the current date, he could use Indirect on the References sheet. That might work! Hopefully, this will help the Tears. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Page 1 of 2 12 LastLast

Posting Permissions

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