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
"JLatham" wrote:
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.
"catlover1946" wrote:
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 viewcode
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
"Roger Govier" wrote:
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
--
Regards
Roger Govier
"catlover1946" wrote in message
...
I've tried one more thing. Open worksheet. Toolsmacrovisual basic
editorviewproject explorerviewproperties window. on last row,
check for
level of visibility of worksheet. -1 is visible, 2 is very hidden.
checking
workbook, it shows password. checking viewcode 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" wrote:
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.
--
Regards
Roger Govier
"catlover1946" wrote in
message
...
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" wrote:
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!!!!
--
Regards
Roger Govier
"catlover1946" wrote in
message
...
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.
"Roger Govier" wrote:
Or FormatCellsNumberCustom;;; (3 semicolons)
--
Regards
Roger Govier
"Biff" wrote in message
...
Another trick is using a font size of 1.
Biff
"JLatham" wrote in
message
...
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.
"catlover1946" wrote:
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?