# Excel Worksheet Reference Problems



## drivas.p (Jan 20, 2010)

Using Excel 2003, I have a formula in one worksheet that is linked to another (=hello!B3). There are three spreadsheets which all are accompanied by their own worksheet with formulas referencing cells in the spreadsheet. Two of my worksheets work completely fine. One however, will work the first time I enter the reference (=hello!B3), and then when i try to change it (=hello!B4) the formula is displayed instead of the number being referenced in the other spreadsheet. I tried using Ctrl + `(accent key), but no luck there.

Any ideas?


Thanks in advance for any help.


----------



## RSpecianjr (Jan 20, 2010)

Hello drivas.p,

Im going to start off by saying... that is odd. haha. Just a few questions. 

Do you have any other formulas on the same worksheet and how do they dsiplay?

what is the value of 'hello!B4'?

What happens if you enter '=hello!B4' into a different cell?

Thanks,

Robert Specian Jr.


----------



## drivas.p (Jan 20, 2010)

I do have other formulas that are referencing the same spreadsheet ("hello") and none of them work when changing the row or column number in the formula. It will work if I delete the cell entirely, but having to do that every time defeats the purpose of having a formula/template.

The values of these formulas are numbers, words, and dates (so basically a lot of different things.)

At first I thought it might be that I'm working from a shared drive, but then I would assume the other two worksheets that have formulas wouldn't work either (and they are working well). It's almost as if when I input a new row or column, the formula bar fails to recognize the equal sign.


----------



## RSpecianjr (Jan 20, 2010)

Hello drivas.p,

There are only four things that I can think of that cause that:

1. There is a leading space before the =.
2. The Value its looking up is what its displaying.
3. Show formula's is on.
4. The cell format that holds the formula's is set to 'Text'.

We know it's not option 2 or 3, and I have enough faith that its not option 1. = P. So that leaves option 4. Try changing the cell formats to 'General', sometimes the results need to be refreshed. To Refresh the results, select the cell pressing 'F2' then 'Enter'.

Hope this helps,

Robert Specian Jr.


----------



## drivas.p (Jan 20, 2010)

Thanks so much for the help. Turns out that the cell format was on text for some reason and I also needed to have a space between the equal sign and the worksheet/cell designation.

I appreciate your assitance.


----------



## drivas.p (Jan 20, 2010)

Actually I spoke too soon  Looks like the cells won't maintain the formatting that they were in when I try to change the row/column number. 

Could this be a copy paste issue? I had started a new worksheet and avoided the copy paste as a possible solution to the overall problem but perhaps these cells maintain a certain formatting?

Pete


----------



## RSpecianjr (Jan 20, 2010)

Hey Pete,

It could be a copy paste issue. Are you copying a cell containing the formula, Dragging the formula down, or are you just editing the existing one?

'Paste Special' is an amazing technique. Normally when you paste it pastes a duplicate of the cell copies (formulas being offset, unless using static). With 'Paste Special', you can paste any part of the copied cell. In this case you may want to 'Paste Formula' and see if that helps. Assuming the destination cell is not formatted as 'text'.

When dragging the formula down or over, it should give you a small pop-up box allowing you to choose the option 'Fill Without Formatting'. Just make sure the destination cells are set to 'General' to begin with.

Im unaware of a preference to autodetect and adjust formats, so it shouldn't change the format if your just editing the formula.

Hope this helps,

Robert Specian Jr.


----------

