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

"Mike D." wrote in message ...
| 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

"Niek Otten" wrote in message ...
| 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
|
| "Mike D." wrote in message ...
|| 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 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

"Niek Otten" wrote in message ...
| 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
|
| "Mike D." wrote in message ...
|| 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

Thanks for your help, Niek.

"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

"Niek Otten" wrote in message ...
| 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
|
| "Mike D." wrote in message ...
|| 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.
|
|

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


"Dave Peterson" wrote in message ...
| 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
|
| "Niek Otten" wrote in message ...
| | 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
| |
| | "Mike D." wrote in message ...
| || 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.

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

"Dave Peterson" wrote in message ...
| 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
|
| "Niek Otten" wrote in message ...
| | 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
| |
| | "Mike D." wrote in message ...
| || 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

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

"Dave Peterson" wrote in message ...
|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
|
| "Dave Peterson" wrote in message ...
| | 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
| |
| | "Niek Otten" wrote in message ...
| | | 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
| | |
| | | "Mike D." wrote in message ...
| | || 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.

Niek Otten wrote:

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

"Dave Peterson" wrote in message ...
|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
|
| "Dave Peterson" wrote in message ...
| | 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
| |
| | "Niek Otten" wrote in message ...
| | | 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
| | |
| | | "Mike D." wrote in message ...
| | || 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

--

Dave Peterson