Recalculation?

M

Mike D.

Hi. I have a formula that is summing items in several worksheets. The
formula is:

=SUM('A:R'!D54)

The value that this formula shows is zero even though there is data in the
other cells. If I enter into this cell (by pressing F2) and do nothing more
than press the enter key, the value changes to correctly sum up the values.

Does anyone understand why this is occurring? Recalculation is set to
Automatic and pressing F9 does not fix this.

I have this formula replicated in about 1,000 locations throughout this
workbook.

Thanks,
Mike.
 
N

Niek Otten

Hi Mike,

Try rebuilding the dependency tree by pressing CTRL+ALT+SHIFT+F9 or, in older versions, by replacing all "=" by "="

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi. I have a formula that is summing items in several worksheets. The
| formula is:
|
| =SUM('A:R'!D54)
|
| The value that this formula shows is zero even though there is data in the
| other cells. If I enter into this cell (by pressing F2) and do nothing more
| than press the enter key, the value changes to correctly sum up the values.
|
| Does anyone understand why this is occurring? Recalculation is set to
| Automatic and pressing F9 does not fix this.
|
| I have this formula replicated in about 1,000 locations throughout this
| workbook.
|
| Thanks,
| Mike.
 
N

Niek Otten

Hi Mike,

BTW, I don't think you need the quotes around the sheet names, if there aren't any spaces in those names. I don't think it will
make a difference, but you might try.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Mike,
|
| Try rebuilding the dependency tree by pressing CTRL+ALT+SHIFT+F9 or, in older versions, by replacing all "=" by "="
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| Hi. I have a formula that is summing items in several worksheets. The
|| formula is:
||
|| =SUM('A:R'!D54)
||
|| The value that this formula shows is zero even though there is data in the
|| other cells. If I enter into this cell (by pressing F2) and do nothing more
|| than press the enter key, the value changes to correctly sum up the values.
||
|| Does anyone understand why this is occurring? Recalculation is set to
|| Automatic and pressing F9 does not fix this.
||
|| I have this formula replicated in about 1,000 locations throughout this
|| workbook.
||
|| Thanks,
|| Mike.
|
|
 
D

Dave Peterson

Excel added them if I didn't (xl2003).


Niek said:
Hi Mike,

BTW, I don't think you need the quotes around the sheet names, if there aren't any spaces in those names. I don't think it will
make a difference, but you might try.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Mike,
|
| Try rebuilding the dependency tree by pressing CTRL+ALT+SHIFT+F9 or, in older versions, by replacing all "=" by "="
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| Hi. I have a formula that is summing items in several worksheets. The
|| formula is:
||
|| =SUM('A:R'!D54)
||
|| The value that this formula shows is zero even though there is data in the
|| other cells. If I enter into this cell (by pressing F2) and do nothing more
|| than press the enter key, the value changes to correctly sum up the values.
||
|| Does anyone understand why this is occurring? Recalculation is set to
|| Automatic and pressing F9 does not fix this.
||
|| I have this formula replicated in about 1,000 locations throughout this
|| workbook.
||
|| Thanks,
|| Mike.
|
|
Click to expand...
 
N

Niek Otten

Indeed! Not with names like Sheet1, Sheet2, etc, but it did with A:C. Not with A:D, however.

No idea what thought is behind this.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Excel added them if I didn't (xl2003).
|
|
| Niek Otten wrote:
| >
| > Hi Mike,
| >
| > BTW, I don't think you need the quotes around the sheet names, if there aren't any spaces in those names. I don't think it
will
| > make a difference, but you might try.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Hi Mike,
| > |
| > | Try rebuilding the dependency tree by pressing CTRL+ALT+SHIFT+F9 or, in older versions, by replacing all "=" by "="
| > |
| > | --
| > | Kind regards,
| > |
| > | Niek Otten
| > | Microsoft MVP - Excel
| > |
| > || Hi. I have a formula that is summing items in several worksheets. The
| > || formula is:
| > ||
| > || =SUM('A:R'!D54)
| > ||
| > || The value that this formula shows is zero even though there is data in the
| > || other cells. If I enter into this cell (by pressing F2) and do nothing more
| > || than press the enter key, the value changes to correctly sum up the values.
| > ||
| > || Does anyone understand why this is occurring? Recalculation is set to
| > || Automatic and pressing F9 does not fix this.
| > ||
| > || I have this formula replicated in about 1,000 locations throughout this
| > || workbook.
| > ||
| > || Thanks,
| > || Mike.
| > |
| > |
|
| --
|
| Dave Peterson
 
D

Dave Peterson

I bet excel doesn't want to get confused between the worksheets and the range
A:R.
 
N

Niek Otten

That's what I thought too, at first. But then, why is A:D without quotes? And many other combinations, BTW.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I bet excel doesn't want to get confused between the worksheets and the range
| A:R.
|
| Niek Otten wrote:
| >
| > Indeed! Not with names like Sheet1, Sheet2, etc, but it did with A:C. Not with A:D, however.
| >
| > No idea what thought is behind this.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Excel added them if I didn't (xl2003).
| > |
| > |
| > | Niek Otten wrote:
| > | >
| > | > Hi Mike,
| > | >
| > | > BTW, I don't think you need the quotes around the sheet names, if there aren't any spaces in those names. I don't think it
| > will
| > | > make a difference, but you might try.
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | > | Hi Mike,
| > | > |
| > | > | Try rebuilding the dependency tree by pressing CTRL+ALT+SHIFT+F9 or, in older versions, by replacing all "=" by "="
| > | > |
| > | > | --
| > | > | Kind regards,
| > | > |
| > | > | Niek Otten
| > | > | Microsoft MVP - Excel
| > | > |
| > | > || Hi. I have a formula that is summing items in several worksheets. The
| > | > || formula is:
| > | > ||
| > | > || =SUM('A:R'!D54)
| > | > ||
| > | > || The value that this formula shows is zero even though there is data in the
| > | > || other cells. If I enter into this cell (by pressing F2) and do nothing more
| > | > || than press the enter key, the value changes to correctly sum up the values.
| > | > ||
| > | > || Does anyone understand why this is occurring? Recalculation is set to
| > | > || Automatic and pressing F9 does not fix this.
| > | > ||
| > | > || I have this formula replicated in about 1,000 locations throughout this
| > | > || workbook.
| > | > ||
| > | > || Thanks,
| > | > || Mike.
| > | > |
| > | > |
| > |
| > | --
| > |
| > | Dave Peterson
|
| --
|
| Dave Peterson
 
D

Dave Peterson

Ehhhhhhhhhh.

Yucccccck.

I got nothing!

R is used in R1C1 reference style, so maybe it's always on the look out for
R's??? A:C gets the apostrophes, too.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Would like to input 3 letters into a cell, each having a different numerical value 5
excel cells changing to "#VALUE!" whenever another file is opened 8
Functions not firing! 2
#VALUE problem 3
Formula Result = Volatile ?? 3
Disabling formula recalculation when opening or closing workbooks 3
Inserting worksheet makes formulae stop working 5
recalculation not recalculating some cells containing user functio 2

Top