Recalculation?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
|
|
 
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...
 
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
 
I bet excel doesn't want to get confused between the worksheets and the range
A:R.
 
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
 
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.
 
Back
Top