XL 2003, True, False, 1, 0

[Harry Sampson]

Hi,

I am using XL 2003 and whenever I enter TRUE or FALSE into a spreadsheet
cell, it immediately turns to 1 or 0. I made sure that the number format is
general. And I created a "boolean" style, again putting the number format
to general. But I still get only 1 or 0 displayed in the cell, even though
the formula bar entry shows TRUE or FALSE. How do I get the spreadsheet to
display TRUE or FALSE?

I inherited this spreadsheet, so I am not sure what setting caused this to
happen.

Best regards,
Kevin

 

[CaptainQuattro]

Tools > Options > Transition

Transition formula evaluation may be checked.

Caution: If transition formula evaluation is checked, there may be
formulas in your spreadsheet that require it in order to calculate
correctly.

 

[Harry Sampson]

Captain,

Nope, it ain't checked. Everything is blank.

Other ideas?

 

[Peo Sjoblom]

It can be either of these 3 IMHO,
transition formula evaluation under>tools>option>transition (most likely,
check again)
Autocorrect (not very likely)
Event macro (not very likely)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Harry Sampson]

Hi,

Hmm...

This is a tough one.

Tools > Options > Transition

Save Excel files as Microsoft Office Excel Workbook

Microsoft Office Excel menu key: /
No check Transition navigation keys
No check Transition formula evaluation
No check Transition formula entry

So I can rule that one out.

On this workbook, there are three worksheets. I discovered on the first
worksheet, that "True" and "false" do show up correctly in the cells.

On the third worksheet, TRUE and FALSE automatically evaluate to 1 or 0,
even though the formula bar shows TRUE and FALSE.

There are no macros and I don't think autocorrect would work because then
the above statement wouldn't hold true about formula bar and cell display.

I tried copying "TRUE" from worksheet one to worksheet three. I copy
*everything* (formula and format). It still shows 1 in the cell.

So I am puzzled. I would like the cell display to show "TRUE".





Peo Sjoblom wrote...

 

[CaptainQuattro]

Something you may want to try:

Before attempting the following make a backup of you file and move it
somewhere safe.

Insert a new worksheet

Copy the entire "broken" worksheet to the clipboard

Paste Special > Formulas to Cell A1 of the new worksheet.

If TRUE and FALSE work correctly on the new worksheet, copy and "Paste
special" the formats and column widths from the broken worksheet to the
new version.

Next, you will need to use Edit > Replace (Or Ctrl-H) to Find all
references to the broken page on each of the other 2 worksheets, and
replace them with references to the new page.

Finally, you can delete the broken page and bestow its name on the new
version.

 

[Guest]

But did you go to the 3rd worksheet when you checked it? These settings are
at the sheet level so that if sheet 1 is active you won't see anything but if
you change to sheet 3 it will be checked?


Peo

 

[Harry Sampson]

Hi,

Yes, I think there was some corruption. I modified your approach by using a
new workbook and copying all the sheets using the steps you provided. And
then I had to adjust the links.

Thanks.


"CaptainQuattro"

 

[Harry Sampson]

Peo, good work. Yes, it is sheet specific. When I went to sheet 3, I noted
the check mark. Although I thought I had checked before. Obviously not.
In any event, I recreated the workbook, so the problem has been solved,
albeit using more force than necessary. Thank you