Reference cell error

[da]

Hi
When I use the following formula, I get #REF error.
How can I fix it?
Thank you

=SUM('Daily Sworn Status'!M8)

 

[p45cal]

probably a misspelling of the sheet name

Hi
When I use the following formula, I get #REF error.
How can I fix it?
Thank you

=SUM('Daily Sworn Status'!M8)

 

[Gord Dibben]

First of all you don't need the SUM part since you are summing nothing, only
referring to one cell.

Did you mean ='Daily Sworn Status'!M8

But the REF error must come because you don't have a sheet named

Daily Sworn Status


Gord Dibben MS Excel MVP

 

[Dave Peterson]

And if it's not a typo in the sheet name...

Any chance that you have a #Ref! in M8 of that sheet?

 

[da]

Thank you to all who responded.
Yes, I do have a worksheet that is named "Daily Sworn Status', which is in
the same file.
I also tried to change the formula by taking out Sum, and still I get the
same error#ref. And I also checked the spelling and it is correct (I copied
the name of the worksheet to avoid spelling mistake.)

It is a mystery which took all my yesterday.

 

[p45cal]

no trailing/leading spaces in the sheet name?


Thank you to all who responded.

Yes, I do have a worksheet that is named "Daily Sworn Status', which i in
the same file.
I also tried to change the formula by taking out Sum, and still I ge the
same error#ref. And I also checked the spelling and it is correct ( copied
the name of the worksheet to avoid spelling mistake.)

It is a mystery which took all my yesterday.

 

[Dave Peterson]

Rename that sheet to: A
(Just a single letter)

Then check your formula. If the formula adjusted to the new name, then I don't
have a guess.

But my guess is that the formula stayed the same.

Now change your formula to:
='A'!M8

Did that evaluate correctly?

If it did, then change the name of the sending sheet back to what you want.

(I bet you'll find that if you look closely, there was a typo between the
formula and the sheetname.)

========
Another way to build that formula without doing any typing.

Select M8 on the sending sheet
Edit|copy
Then select the cell that should get the formula on the other sheet
Edit|paste special|click on Paste Link

 

[da]

Thanks
I made sure there is no space or spelling mistake. I copied the worksheet
name. Still, same error.

 

[da]

Thanks
You were right. There was a trailing space which I deleted and the formlua
worked.
Thank you

 

[da]

Thank you. There was a trailing space in the worksheet name and that is why
formula gave an error.
It works now.
Thanks for your suggestion and help.