MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is there something obvious I'm missing here??! Please help.

Posted by andrew miller on January 16, 2002 2:11 PM

I have a cell that references a cell in a worksheet within the same workbook. It is a very simple formula. it simply is =(sheet next to it)(cell). It works fine in the cell that it is in. My problem is that if I try to enter that same formula into a cell in any columns to the right or left, it returns a #value! error. It makes absolutely no sense. I have manually typed it in, I have copied and pasted. Nothing works. It is the same thing anywhere on the page. I created a new worksheet and entered the same formula referencing the same cell I get the same error. So either I am missing something very obvious or I think I have a corrupted file. Any thoughts?

Posted by Adam S. on January 16, 2002 2:25 PM


Can you give us a specific example? Please specify the cell your formula is typed in as well as the formula that works in that cell. Then show the formula in the cell to the left where you get the #Value.

Adam S.

Posted by Joe Was on January 16, 2002 3:19 PM

your code should be:


in format, where you are entering the formula on a sheet not named "Sheet2" in my example and "A1" is the cell value or string you want to return to your new cell from A1 on sheet 2.

If this does not work. I fix the problem by checking; what the cell format is, some data must be formatted the same in both the calling and the sending cell?

Or, the cell needs to be formatted "General." JSW

Posted by Chris D on January 17, 2002 11:58 AM

nothing's "merged" is it ?