reference to other worksheets in FormulaR1C1

[Guest]

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

 

[Guest]

What happends if you change your formula to

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

 

[Guest]

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])

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

 

[Guest]

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])

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

 

[Guest]

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?

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])

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

 

[Guest]

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])"

 

[Guest]

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