find hidden data in worksheet

[Guest]

Excel 2003. Windows XP Professional. Bank reconciliations. How is it
possible in an unprotected worksheet to hide additional data input in
formulas so that visible invalid numbers produce accurate results? Displaying
hidden formulas in formula bar reveals nothing. Blank cells have been
included in formula, but searches for hidden numbers and links in these cells
produced nothing. I'm at my wit's end to correct this misuse of Excel in my
office. Any suggestions?

 

[Guest]

Maybe the empty cells aren't really hidden? Perhaps they have text color set
to background color, making them appear empty unless you select them at which
time their contents will be revealed.

 

[Biff]

Another trick is using a font size of 1.

Biff

Maybe the empty cells aren't really hidden? Perhaps they have text color
set
to background color, making them appear empty unless you select them at
which
time their contents will be revealed.

 

[Roger Govier]

Or Format>Cells>Number>Custom>;;; (3 semicolons)

 

[Guest]

I first checked to see what text color was used, and converted the entire
worksheet to black type face. All cells have only white background. None
are filled with colored background. Even if text and background color are
the same, the formula should be displayed in the formula bar when the cell is
selected. Since formulas in the formula bar can be hidden, I re-displayed
formula bar contents for the entire workbook.

 

[Guest]

Biff --what a great idea, and probably the only thing I have overlooked in
searching cells.

 

[Guest]

Using font size 1 still leaves a tell-tale data mark in the cell. I can't
seem to find a way of using a smaller font. Also, the formula is still
displayed in the formula bar. Un-hiding the contents of the formula bar
revealed an empty cell. In order to reconcile a bank statement, all numbers
must be correct. The weird thing is that the numbers displayed add up to the
exact number needed to make the bank statement balance. But I know for a
fact the numbers used are incorrect. Is there any way to "overwrite" a cell,
but use the number underneath?

 

[Guest]

Thanks for the input. It is good to know. Doing this hides the cell
contents, but the formula bar still displays contents of the cell, unless you
hide the information displayed in the formula bar, and this trick is easy to
reverse, which I did. Nothing.

 

[Gord Dibben]

I am confused

You say the worksheet is unprotected yet you claim to be able to unhide contents
in the formula bar.

If the sheet is unprotected, the formula bar contents WILL NOT be hidden.


Gord Dibben MS Excel MVP

 

[Guest]

Of course you're correct. Thanks for pointing out this discrepancy. I hid
and unhid the formula display repeatedly in the desparate hope something
would click, since nothing else had worked. Protection is absolutely
necessary to complete this process. It was not my intent to put forth any
incorrect or misleading information. I'm an accountant, so financial
programs are my forte. In the "use it or lose it" watchlist, I apparently
lose it. I obiously remain a novice since Excel is not the primary software
I use. I've tried so many things I've confused myself, and forget where I've
been. I appreciate any input of whatever type. I'm amazed at the helpful
suggestions I've recieved so far.
Thanks for your insight,
Catlover1946

 

[Roger Govier]

Hi

OK, then for something really bizarre, are any of the cells being added
Merged cells?
Try the following.
Put values in a set of adjacent cells down 2 columns.
Now, merge two empty horizontal cells in the same 2 columns and then use
the Format Painter to change the formats of the cells with your pairs of
data.
Only the left set of numbers in the newly merged cells will show up, but
if you highlight the cells and look at the sum in the calculation bar at
the bottom of the sheet, it will show the total of not just the visible
values on the screen, but also the "invisible" values in the merged
cells.

I remembered seeing a reference to this in the NG's before, and David
McRitchie uncovered the reason why in the thread as shown below.
http://snipurl.com/swz8 (thanks Gord!!)

It can also be "used" to hide formulae, so maybe there is a hidden
formula that "adjusts" the reconciled figure.
Could be a good way of "fiddling the books" and hiding the
discrepancy!!!!

 

[Guest]

Roger--what wonderful suggestions. I tried this "merged cells" with no luck,
and also the borders diagonally. Neither turned up anything. I did,
however, find in the computer files that for several months, there are two
bank reconciliations. One set has incorrect file names, so that there is an
unofficial reconciliation under a non-identifying file name, and an official
"balanced" reconciliation under a file name in which the contents are easily
recognizable. There is no reason for two reconciliations. It either
balances or it doesn't. Last year I did a reconciliation behind this person
and found completely made-up check amounts which didn't exist any where.
Subsequently this person stopped listing outstanding checks and just put in
totals of outstanding checks, making them impossible to trace. They did
include a calculator tape which supported the amount of O/S checks. The tape
is impossible to verify since it refers to nothing except amounts. I'm
wondering with two sets of working papers if there is a hiddden link or
reference. I only discovered this today, so have not had a chance to pursue
this.

Thanks so much for your input. This information may very well help uncover
future problems. This is all fascinating, and again I thank you for this
information.

 

[Roger Govier]

Well, good luck with your searching.
Sorry we haven't been able to throw any light on the discrepancy, but
when you do uncover it, be sure to post back to the thread and let us
know what the cause was.

 

[Guest]

Roger--thanks!! your support is wonderful, and I love uncovering new
processes to try, and I know I will be able to use them in a future
undertaking.

 

[Guest]

I've tried one more thing. Open worksheet. Tools>macro>visual basic
editor>view>project explorer>view>properties window. on last row, check for
level of visibility of worksheet. -1 is visible, 2 is very hidden. checking
workbook, it shows password. checking view>code for each sheet shows
sub-folders. this is a VBA project and I'm in over my head. All I want to
do is a bank reconciliation. Add and subtract. not rocket science. Why 16
worksheets for 12 months? duplicate months viewed side by side appear
identical. Why sub-folders to add and subtract? Any way to access
sub-folders?
Thanks, MMO

 

[Roger Govier]

Hi

I'm not sure what you mean by sub-folders.
If you want to mail me a copy of the file direct, I will be happy to
take a look and see if I can throw some light on the matter.
To email me direct, remove NOSPAM from my email address

 

[Guest]

Roger--thanks for hanging in with a novice. I'm not sure what is meant by
subfolders, either. I checked the security settings on the office computer
and found that in the folder "Treasurer's files" I have permission to view
only that folder and no subfolders or files. When in VBE, I did view>code
and every worksheet had actions done through a sub-folder. I have no idea
how or why this was set up. Reconciling a bank statemement is a
straightforward process of adding and subtracting. on the June 2006
reconciliation I will make a note or two to point out why thebank statemetn
couldn't possibly balance. I was unaware of the fact this is a VBA project
until I went into VBE. There are a lot of duplicate files saved on the
office computer, which makes for more confusion. There is a file for 2005, a
file for 2006, but in the 2006 file the 2005 information is available because
it is a VBA project. Then several months . One January reconciliation
showed we were more than $7000 out of balance, then miraculously another
reconciliation appeared which showed we balanced to the penny. While getting
my MBA, I did some programming in BASIC, so with a little refreshment I may
be able to unravel this. Properties show that a lot of these documents were
created at Heifer International as opposed to St. Michael's. I imagine she
took a process they used and just made a new VBA project from what template
they used, which would explain the document being originated at Heifer.
Sorry to ramble on. Will attach only the VBA file, so will not cause a lot
of confusion with extra "stuff". Thanks for taking a look at this.
mmo

 

[Guest]

I think Roger is probably more than capable of analyzing the code, but if
either of you want's another hand at it, I've got reply notify set on this
posting and I'd be glad to try to help.

I kind of agree with the ??? about VB coding in a straight ledger type of
setup - but they may have coded some stuff as 'helper' code, and like you
say, trying to follow the model of the previous setup from HI may be the
reason -- perhaps the skill didn't exist to modify it in some ways and they
were forced into setting up the entire scheme as it was provided to them.

The company work for is routinely audited by the DCAA and I guarantee if we
'hid' some calculations from them, there would be serious issues raised. I'm
working now to create a middle-ware application to process corporate
timesheet data for entry into the accounting system, and we're taking great
precautions on it to make sure that nothing is hidden - and I have suggested
to the corp sr management that they either get a consultant or other source
within the company to review all code that I develop, just so they can rest
assured that I haven't done something like "take $0.25 from all employees;
add said $0.25 (* #ofEmployees) to programmer's Gross Pay" every payday or
something like that. But the code project will be locked down with password,
that will be stored in secure area so that only authorized individuals will
have access to it.

 

[Roger Govier]

Hi

Maybe you sent me the wrong file.
I cannot see a single line of code in any worksheet, nor This Workbook,
nor is there any added module, user form or class module within the
workbook.
You must be looking at a different file to the one you mailed me.

 

[Guest]

Roger has offered good insights. The major problem is that I really don't
know what I'm doing in Excel. I recently discovered that at one time when
our bank statements were issued from the middle of one month to the other
(1/18/05 thru 2/17/05) rather than one month from 1/1/05 thru 1/31/05, she
was using the 1/31 date from the GL, and the 1/17 date from the bank
statement. A first year accounting student would know this is like comparing
apples and oranges. There is so much stuff to review, and I seem to have so
little time to work on this. The problems seem endless. I have played with
the worksheets so much, I forget what's on each one, although I try to save
with a name to indicate what they are. I get macro warnings where no macros
exist, even when workbook says "contains macros", with security lowered. I
do not believe at this point that there is any hidden data, but I'm still
having a hard time understanding what's going on."The best indicator of
future behavior is past behavior." She previously "made up" amounts to enter
in the reconciliation to force it to balance. It's just a matter of time
until I find this problem. I will probably obtain new copies of workbooks
which I have not "fiddled with" so I can take a fresh look with the original
data. Thanks for the offer. I'll see what shows up with a fresh look at the
material.
mmo