Finding Formulae

[DQR]

I have a vast data set containing numbers for the most part, but with a few cells somewhere in the middle of it containing formulae which generate numbers. I therefore can't distinguish the numbers from the calculated cells just by looking at the worksheet.

Is there a way I can use the conditional formatting facility (or any other way) to paint the cells containing formulae a different colour, so I can easily see where they are? I know I could use the auditing tools to find them by asking it to show all the links, but I'd like to set it up so that it's instantly printable as a report without arrows all over it.

Cheers,
DQR

 

[SkipVought]

Hi,

One way is to toggle the Window Options/Formulas in menu item Tools/Options/View




Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

Skip's suggestion is a good one. (Hi Skip ;-))

To make it easier, though, you can use a keyboard shortcut... The following acts as a "toggle" - to turn it on, then off.

Hold down the <Alt> key, and hit the key located above the <Tab> key and to the left of the &quot;1&quot; key.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Hi Dale, my friend -- thot you had fallen off the edge of the world!?

Turn around

Nice to see your back! HA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DQR]

Ah yes - this solves the problem as I outlined it: many thanks. I'd still like to be able to do it with the conditional formatter if possible though, because the switch to viewing formulae changes all the cell widths, negating the effect of any previous formatting I've done to the worksheet prior to printing it.

I'd thought that the TYPE function might have helped with this problem, since when applied to a formula TYPE is supposed to return a 16, as opposed to the 1 that it returns for a number - and I could have used that difference to distinguish between the two. It doesn't seem to work that way however: TYPE returns a 1 for a cell containing either a number or a formula. Is there perhaps a function which will return a text version of the formula in any given cell? If there is, I might be able to get at it that way.

Any more suggestions gratefully received.
Cheers,
DQR