Help! I have a cell that has no formula - formatted as text. But as soon as
I type a word into a second text formatted cell that first one autofills with
a formula that makes it equal to the second one.

i.e. if I type "test" into cell A12, then cell R12 automatically writes the
formula =$A12

And it's only on certain cells, not the whole sheet. I have no formula in
either cell. It's like conditional formatting only with formulas.

I've tried copying a non-wonky cell & pasting it over the afflicted one.
This works while the sheet is open, but as soon as I close & reopen it, it's
back. What would cause this to happen? the cells in reference are
DataValidation cells, but otherwise I have no code or macros associated with
this sheet.

There are conditional formats that use functions, are you sure this isn't
happening? And are you sure there are no macros, if when you open the
workbook it asks if you want to enable macros you may have. If so try
disabling and then check for this occuring again. What hapens if you just
copy the entire sheet to another workbook?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"slreilly" wrote:

Help! I have a cell that has no formula - formatted as text. But as soon as
I type a word into a second text formatted cell that first one autofills with
a formula that makes it equal to the second one.

i.e. if I type "test" into cell A12, then cell R12 automatically writes the
formula =$A12

And it's only on certain cells, not the whole sheet. I have no formula in
either cell. It's like conditional formatting only with formulas.

I've tried copying a non-wonky cell & pasting it over the afflicted one.
This works while the sheet is open, but as soon as I close & reopen it, it's
back. What would cause this to happen? the cells in reference are
DataValidation cells, but otherwise I have no code or macros associated with
this sheet.

Uncheck ToolsOptionsEdit tab, "Extend data range formats and formulas"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"slreilly" wrote in message ...
| Help! I have a cell that has no formula - formatted as text. But as soon as
| I type a word into a second text formatted cell that first one autofills with
| a formula that makes it equal to the second one.
|
| i.e. if I type "test" into cell A12, then cell R12 automatically writes the
| formula =$A12
|
| And it's only on certain cells, not the whole sheet. I have no formula in
| either cell. It's like conditional formatting only with formulas.
|
| I've tried copying a non-wonky cell & pasting it over the afflicted one.
| This works while the sheet is open, but as soon as I close & reopen it, it's
| back. What would cause this to happen? the cells in reference are
| DataValidation cells, but otherwise I have no code or macros associated with
| this sheet.
|

Hi,

Click the sqaure on the top left corner next to Column A1 to select the
worksheet cells or you can press the CTRL key and click on each Worksheet tab
to group the selected worksheet. Then do the following:

1. On the Format menu, click Cells.
The Format Cells dialof box is displayed.
2. Click the Number tab.
3. Under the Category list, slect General.
4. Click Ok to apply the settings and return to the Workshet.

Note: Right-click the Worksheet tab and select Ungroup Sheets to deselect
the Worksheet tab.

Challa Prabhu

"slreilly" wrote:

Help! I have a cell that has no formula - formatted as text. But as soon as
I type a word into a second text formatted cell that first one autofills with
a formula that makes it equal to the second one.

i.e. if I type "test" into cell A12, then cell R12 automatically writes the
formula =$A12

And it's only on certain cells, not the whole sheet. I have no formula in
either cell. It's like conditional formatting only with formulas.

I've tried copying a non-wonky cell & pasting it over the afflicted one.
This works while the sheet is open, but as soon as I close & reopen it, it's
back. What would cause this to happen? the cells in reference are
DataValidation cells, but otherwise I have no code or macros associated with
this sheet.

That was it. It was walking the formula down from the 2 cells above. I didn't
realize Excel would do that.

Thank you!
slr

"Niek Otten" wrote:

Uncheck ToolsOptionsEdit tab, "Extend data range formats and formulas"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"slreilly" wrote in message ...
| Help! I have a cell that has no formula - formatted as text. But as soon as
| I type a word into a second text formatted cell that first one autofills with
| a formula that makes it equal to the second one.
|
| i.e. if I type "test" into cell A12, then cell R12 automatically writes the
| formula =$A12
|
| And it's only on certain cells, not the whole sheet. I have no formula in
| either cell. It's like conditional formatting only with formulas.
|
| I've tried copying a non-wonky cell & pasting it over the afflicted one.
| This works while the sheet is open, but as soon as I close & reopen it, it's
| back. What would cause this to happen? the cells in reference are
| DataValidation cells, but otherwise I have no code or macros associated with
| this sheet.
|

In microsoft.public.excel.misc on Sat, 4 Aug 2007, slreilly
wrote :

"Niek Otten" wrote:

Uncheck ToolsOptionsEdit tab, "Extend data range formats and formulas"


That was it. It was walking the formula down from the 2 cells above. I didn't
realize Excel would do that.

Thank you!
slr

I've noticed Excel doing that too, but had no idea why until now. :)
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)