Referencing sheets with FORMULAS (Excel 2000 (9.0.2720))

Topic

New Reply

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?

Reply | Quote

Replies

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

=INDIRECT("[CoreData.xls]" & TEXT(NOW(),"Mmmm") & "!$A$1")

Reply | Quote

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

Reply | Quote

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????

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The supplied Microsoft Excel Add-In “Update Add-in Links” does this … umm, doesn’t it? (I don’t use it.)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

> 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.

Reply | Quote

This time I tested it. Here’s what I would do:

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

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

Reply | Quote

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′!
Reset your Summary.xls WB so it reads the data from the Reference sheet, i.e., ='[CoreData]Reference’!
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

Reply | Quote

> 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′!
> Reset your Summary.xls WB so it reads the data from the Reference sheet, i.e., ='[CoreData]Reference’!

> 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

Reply | Quote

[indent]

---

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.

---

[/indent] I must have misunderstood that piece: I thought all he ever did was swap the names of the two sheets. In that case you are right, using my “Reference” sheet idea with =INDEX will do what he wants without your nice VBA bit (which I added to my collection).

Reply | Quote

Wonderful people,

There are only ever 3 sheets (the last one is insignificant). I have to have the sheet names labeled as the month they are referring to.

The 1st sheet contains my “Prior Quarter’s data” – 0106
The 2nd sheet contains my “Current Quarters data” – 0109

In December I will copy the data from the 2nd sheet onto (over) the data on the 1st sheet and re-label the 1st sheet as 0109. Range Names copied are FGU_CURR & COL_CUR

Then I input my data for December onto the 2nd sheet and re-label it 0112.

The “INDIRECT” formula does work BUT I have to have all of the source data files open (there are about 30 of these, so i’d rather not).

I’ve attached some sample files in a zip file for those that wish to look.

Thanks people

Reply | Quote

Did you note that Sam’s idea was to use =INDIRECT on the extra sheet called “Reference” within WB CoreData.xls, rather than use =INDIRECT in WB Summary.xls in reference to WB CoreData.xls? This avoids the open WB problem, and lets you use “Update Add-in Links…” to refresh Summary.xls without opening WB CoreData.xls.

Reply | Quote