reference to other worksheets in FormulaR1C1

Hi All,
I want to create a formula:
ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"
where '01', '02', '03' are worksheet names in the same workbook. Recording a 
macro gives the same reference format to other sheets in the workbook. If I 
create this formula manually, it works after re-opening the workbook.

However, if I create the formula with the line above, Excel interprets the 
'01'!, '02!', '03!' references as those to other workBOOKS instead of 
workSHEETS. It informs me that the workbook contains references to other 
workbooks, and it asks me if I want to refresh ... .
What's wrong? How can I create a workSHEET reference with 
ActiveCell.FormulaR1C1?

Thanks!
Stefi


0
Stefi (275)
5/20/2005 12:36:10 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
879 Views

Similar Articles

[PageSpeed] 38

What happends if you change your formula to 

=SUM('01:03'!RC[-54])


"Stefi" wrote:

> Hi All,
> I want to create a formula:
> ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"
> where '01', '02', '03' are worksheet names in the same workbook. Recording a 
> macro gives the same reference format to other sheets in the workbook. If I 
> create this formula manually, it works after re-opening the workbook.
> 
> However, if I create the formula with the line above, Excel interprets the 
> '01'!, '02!', '03!' references as those to other workBOOKS instead of 
> workSHEETS. It informs me that the workbook contains references to other 
> workbooks, and it asks me if I want to refresh ... .
> What's wrong? How can I create a workSHEET reference with 
> ActiveCell.FormulaR1C1?
> 
> Thanks!
> Stefi
> 
> 
0
DukeCarey (494)
5/20/2005 12:59:01 PM
It's worse:

=SUM('01:[03]03'!RC[-54])

This is the resulted formula, and it gives (of course) #REF error.

Stefi

„Duke Carey” ezt írta:

> What happends if you change your formula to 
> 
> =SUM('01:03'!RC[-54])
> 
> 
> "Stefi" wrote:
> 
> > Hi All,
> > I want to create a formula:
> > ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"
> > where '01', '02', '03' are worksheet names in the same workbook. Recording a 
> > macro gives the same reference format to other sheets in the workbook. If I 
> > create this formula manually, it works after re-opening the workbook.
> > 
> > However, if I create the formula with the line above, Excel interprets the 
> > '01'!, '02!', '03!' references as those to other workBOOKS instead of 
> > workSHEETS. It informs me that the workbook contains references to other 
> > workbooks, and it asks me if I want to refresh ... .
> > What's wrong? How can I create a workSHEET reference with 
> > ActiveCell.FormulaR1C1?
> > 
> > Thanks!
> > Stefi
> > 
> > 
0
Stefi (275)
5/20/2005 1:16:03 PM
Besides it informs me that the workbook contains references to other 
workbooks, and it asks me if I want to refresh ... .
Stefi

„Stefi” ezt írta:

> It's worse:
> 
> =SUM('01:[03]03'!RC[-54])
> 
> This is the resulted formula, and it gives (of course) #REF error.
> 
> Stefi
> 
> „Duke Carey” ezt írta:
> 
> > What happends if you change your formula to 
> > 
> > =SUM('01:03'!RC[-54])
> > 
> > 
> > "Stefi" wrote:
> > 
> > > Hi All,
> > > I want to create a formula:
> > > ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"
> > > where '01', '02', '03' are worksheet names in the same workbook. Recording a 
> > > macro gives the same reference format to other sheets in the workbook. If I 
> > > create this formula manually, it works after re-opening the workbook.
> > > 
> > > However, if I create the formula with the line above, Excel interprets the 
> > > '01'!, '02!', '03!' references as those to other workBOOKS instead of 
> > > workSHEETS. It informs me that the workbook contains references to other 
> > > workbooks, and it asks me if I want to refresh ... .
> > > What's wrong? How can I create a workSHEET reference with 
> > > ActiveCell.FormulaR1C1?
> > > 
> > > Thanks!
> > > Stefi
> > > 
> > > 
0
Stefi (275)
5/20/2005 1:22:02 PM
I'm just guessing now.

Does the formula work after you create it programmatically and before you 
close the workbook?  Are you sure the sheet is named zero-three and not 
oh-three?

Do you have a workbook named 03?  


"Stefi" wrote:

> It's worse:
> 
> =SUM('01:[03]03'!RC[-54])
> 
> This is the resulted formula, and it gives (of course) #REF error.
> 
> Stefi
> 
> „Duke Carey” ezt írta:
> 
> > What happends if you change your formula to 
> > 
> > =SUM('01:03'!RC[-54])
> > 
> > 
> > "Stefi" wrote:
> > 
> > > Hi All,
> > > I want to create a formula:
> > > ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"
> > > where '01', '02', '03' are worksheet names in the same workbook. Recording a 
> > > macro gives the same reference format to other sheets in the workbook. If I 
> > > create this formula manually, it works after re-opening the workbook.
> > > 
> > > However, if I create the formula with the line above, Excel interprets the 
> > > '01'!, '02!', '03!' references as those to other workBOOKS instead of 
> > > workSHEETS. It informs me that the workbook contains references to other 
> > > workbooks, and it asks me if I want to refresh ... .
> > > What's wrong? How can I create a workSHEET reference with 
> > > ActiveCell.FormulaR1C1?
> > > 
> > > Thanks!
> > > Stefi
> > > 
> > > 
0
DukeCarey (494)
5/20/2005 2:10:03 PM
Stefi
You must identify the sheet '01' etc will just confuse VB 
try the following
 ActiveCell.FormulaR1C1 = 
"=SUM('sheets01'!RC[-54],'sheets02'!RC[-54],'sheets03'!RC[-54])"

"Stefi" wrote:

> Hi All,
> I want to create a formula:
> ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"
> where '01', '02', '03' are worksheet names in the same workbook. Recording a 
> macro gives the same reference format to other sheets in the workbook. If I 
> create this formula manually, it works after re-opening the workbook.
> 
> However, if I create the formula with the line above, Excel interprets the 
> '01'!, '02!', '03!' references as those to other workBOOKS instead of 
> workSHEETS. It informs me that the workbook contains references to other 
> workbooks, and it asks me if I want to refresh ... .
> What's wrong? How can I create a workSHEET reference with 
> ActiveCell.FormulaR1C1?
> 
> Thanks!
> Stefi
> 
> 
0
peter147 (2)
5/20/2005 6:28:28 PM
Sorry boys,

I found out, that the problem was the following:
The sequence of the statements was wrong: creating the worksheets named 01, 
02, 03 FOLLOWED the statement 

ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"

so at the time of creating this formula the referred sheets did not exist yet!

Changing the sequence solved the problem.

However! It would be a more intelligent behaviour if in such a case Excel 
would give an error message "The referred sheet does not exist" instead of 
high-handedly changing the sheet-reference to workbook-reference, because 
workbook-references are clearly distuinguished: [workbook-name]
 
Thanks!
Stefi

 
0
Stefi (275)
5/25/2005 8:01:11 AM
Reply:

Similar Artilces:

How do I convert an existing MS Excel worksheet tracking a simple.
I am a novie Excel user who has tracked the activity of my simple savings account with an Excel worksheet. I would like to set up this existing worksheet to perform autosum functions when I enter deposits, withdrawals, and interest instead of manually doing the math on a calculator to arrive at the balance total after each entry. Hi, NOTANYJOE; If A2 contains your first deposit, and columns B and C are withdrawals and interest respectively, in D2 enter this: =A2-B2+C2 ( Or if you're using negative numbers for withdrawals: =A2+B2+C2 ) In D3 enter this: =A3-B3+C3+D2 ( Or if you're ...

How do I automatically save an Excel Worksheet?
Anyone know if there is a way to automatically save an Excell Worksheet say every 5 minutes? I'm not refering to Auto Recovery. I need an actual save. -- Tony Download and install the AutoSafe Add-in by Jan Karel Pieterse: http://www.bmsltd.ie/MVP/Default.htm (note: Make sure you download AutoSafe.zip, not AutoSaveVBE.zip) In article <21FD75DF-E9A1-4B25-83B3-F8CFC0C5C29A@microsoft.com>, "Tony" <Tony@discussions.microsoft.com> wrote: > Anyone know if there is a way to automatically save an Excell Worksheet say > every 5 minutes? I'm not referin...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

How to synchronize rows between worksheets
I have a workbook with 2 worksheets in it. I would like to synchronize the rows between those worksheets, so that when a row is inserted or deleted in the first worksheet, a row is inserted or deleted in the second worksheet. I would like to do this without using a macro or VB. Is this possible? Howard, No, it isn't possible without VB. Use one sheet and then it isn't an issue. HTH, Bernie MS Excel MVP "Howard Schisler" <howard.schisler@nationalcity.com(donotspam)> wrote in message news:840983D0-E4FB-44CE-886F-4D29531F98CE@microsoft.com... >I have a workb...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

Conditional Formatting across different worksheets 04-28-10
Hi everyone, I am reposting my message, as the previous one was not clear. I think that I have to go to VBA, as it was suggested. Does anyone have the code? The problem is as below: I have two sheets, one that I use as a control (i.e. a back up) that I will call Sheet 2, while Sheet 1 is a duplicate of Sheet 2 and is sent out to different people for quarterly updates. The Excel file that I send out for updates has both Sheet1 and Sheet2, but Sheet2 is hidden. What I want to do is make the cells in Sheet 1 automatically change color if they are edited and no longer match Shee...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

working "down" through worksheets
good morning. I'm looking for a function or macro to combine info from various spreadsheets and return pieces of each to a diifferent worksheet. worksheets A-C are account number worksheets. value in column A1-A25 are sub account-numbers. I would like worksheet D to "look in worksheets A-C, look in columns A1-A25 and return specific values. I'm getting stuck on having excel move successively from one worksheet (only through the last populated cell) to the next worksheet (also only through the last populated cell) throught the next worksheet. Any thoughts? -Brian You can ...

protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this? Hi select the cells for which you want to allow entries. After this goto 'Format - cells - Protection&...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

Worksheet chnage event
Hi, I have the following triggered by a worksheet change (there's some other stuff before this line, but that's all working) which causes an error: Case Is = "Open" Range("a" & Target.Row & ":ac" & Target.Row).Select With Selection.Interior .ColorIndex = xlNone End With Range("o" & Target.Row & ":q," & Target.Row & "t" & Target.Row_ & ":v," & T...

left clicking locks up my worksheet
Just installed excell 2003 and I can't get my mouse to function correctly. Whenever a left click is done it locks up and won't alow me to do anything. We have another pc that will do this but only when you hold the button down. I'm using a wireless, optical mouse Hi Steve don't know if it will solve the problem or not, but i would "reinstall" the mouse - that's if the mouse came installation disk. Cheers JulieD "Steve" <Steve@discussions.microsoft.com> wrote in message news:E933316C-C6C1-42F1-A232-5FD1CF9ECE7B@microsoft.com... > Just in...

Too Many Worksheets
I am stuck with the number of worksheets, but can the worksheet tabs be 'stacked' so that there is more than one row of tabs along the bottom? As an example when I go to Win XP Home Display Properties, I have two rows of tabs to select from in the window, one row is Themes, Desktop ScreenSaver Appearance and then another row is MSI Information (my video card is MSI) MSI Clock, Settings. If this is not possible it sure would be a cool feature for the next Excel. Hi no this is not possible in Excel. You may right-click on the lower left corner to get a list of your tabs -- Rega...

countif function in multiple worksheets
I have been trying to use the countif function to count the number of times a "Y" response occurs in a specific cell in 15 different worksheets that are all in one workbook. ... I have tried =SUM(COUNTIF('1:15'!B51,{"Y","N"}) but I keep on getting a "value error". This formula works in one worksheet but it wont count multiple worksheets. I've been trying to solve this for days by myself but I'm stuck. I'm a new user and really would appreciate any help. Thank you =SUMPRODUCT(COUNTIF(INDIRECT("'"&...

Function to get worksheet name
Its seems pretty simple. 1. which functions returns parameters like worksheet name ? Same way can i get what is file name ? Thanks in advance.. ssuryarao@gmail.com wrote... >Its seems pretty simple. > >1. which functions returns parameters like worksheet name ? > >Same way can i get what is file name ? The only built-in function that returns these is CELL. You could use CELL("Filename",A1) to get both the workbook filename and the worksheet name corresponding to the range reference 2nd argument *IF* you've already saved the file. In that case, it'll retu...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Maximum Worksheets
What is the maximum number of worksheets in a workbook and does this vary per version? Geoff, No maximum any more, though the workbook file gets large, and memory requirements to open it go up. Consider why you need a lot of worksheets -- often data is separated when it works much better to keep it in less worksheets. Look at http://www.smokeylake.com/excel/excel_truths.htm. Read "Data across multiple sheets. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Geoff" <gh@bob.com> wrote in message news:...

Copying worksheets with hyperlinks to named cells
I have a spreadsheet that I am working to collect data for several different products. I developed a skeleton worksheet that will be copied within the same workbook and re-named with each individual product. In the skeleton sheet, I have hyperlinks near the front of the sheet that reference to areas later in the sheet (via named cells). I copied this skeleton sheet and gave the copy another name ('product 1' for example). When I click the hyperlinks in the 'product 1' worksheet, it links back to the 'skeleton' worksheet. Is there an easy way to have the lin...

Reference
Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

Sheet Reference
Is there a formula for listing the current Sheet. I know about the filename cell("filename",A1) which returns the entire path, but I just want the Sheet name to appear. John, You still use the CELL("filename") function, but you need to trim the result a bit. Like this: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99) -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "John" <anonymous@discussions.microsoft.com> wrote in message news:0c6001c3a856$291c9a20$a501280a@phx.gbl... > Is there a formula for listing...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...

R1C1 reference #2
Excel keeps changing to the R1C1 reference style, but only in one of my workbooks (I believe in just one of the worksheets). I keep changing it back (Tools - Options - General - R1C1 Reference Style), but when I move or copy cells in the workbook, it changes back to R1C1. Anyone have an idea? Look at the TOOLS / OPTIONS / SETTINGS is the 'R1C1 References Style' box checked? If not, check it then save the workbook. Not sure if this is your issue but it's worth a try. Good Luck, -- Gary Brown gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com "Tony S" wrote: > Ex...

How do I Shorten Object References?
ThisWorkbook.Sheets("Vessel") I'm always referring to this Sheet and Sheet "Operations" I've seen code where people shorten the objects... How exactly do I do that? Dim ws as worksheet set ws = ThisWorkbook.Sheets("Vessel") ws.Range("A1").value = "Shorten" "Benjamin" wrote: > ThisWorkbook.Sheets("Vessel") > I'm always referring to this Sheet and Sheet "Operations" > I've seen code where people shorten the objects... > How exactly do I do that? Benjamin, Here's ...