Loosing formula when changing row #’s

[Bob Ptacek]

I’m stymied and could use some help.

I have a file that contains data in Sheet1 A1:A3 with values a,b,c. In
Sheet2 B1:B3 I reference Sheet1’s data with =Sheet1!A1 and so on. In Sheet2
if I try change the row reference from A1 to A2 I don’t get the value, I get
the formula displayed =Sheet1!A2. The problem is that I have 2 columns like
this and one works with reference changes the other doesn’t.

I have checked cell formatting and option settings in all cells and they are
all identical. The only clue I’ve seen is that before the attempted change I
can still do a precedent trace but after changing the row # to a valid row
the trace indicates that the cell doesn’t “contain a formula with a valid
referenceâ€. The formula/link is getting clobbered, but I can find no
settings that would effect that and would appreciate any hints.

 

[Dave Peterson]

If you never see any value, maybe you're looking at Formulas.

In xl2003, you can toggle between formulas and values by:
tools|Options|view tab|check/uncheck Formulas

Or you can use ctrl-` (control-backquote to the left of the 1/! on my USA
keyboard) for that same toggle.

If this isn't it, then...

Saved from a previous post.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted). I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

 

[Bob Ptacek]

Thank you Dave. It was a difference between General and Text formatting. The
source has a mixture that's random and sometime it worked. By reformatting
the source sheet to General, all is well. Again thanks