Re: Excel data consolidation question

aaron.kempf@xxxxxxxxx wrote...
....
how does the manager import 30 spreadsheets and consolidate numbers out
of all 30 workbooks?

http://www.google.com/groups?selm=hkQVb.2432%24_4.259%40www.newsranger.com

Use the first approach.

C) what happens when someone enters 02042006 instead of 2/4/2006 in a
column?

They get a bunch of #'s rather than a date. If the spread*** is
well-written (so something you couldn't manage), the date could
validated using a formula like

=COUNT(1/(ABS(YEAR(DateEntry)-1960)<=60))

which evaluates to 1 if the year of DateEntry is within 60 years of
1960, so 1900..2020, which seems a reasonable range in 2006. Simple
enough to check narrower, more recent ranges. And using this validation
formula, ad hoc diagnostic messages could be implemented with formulas
like

=IF(ValidDateEntry,"","Invalid date entry in cell
"&CELL("Address",DateEntry))

Excel CHOKES on this simple type of data mismatch

Yes, it would if you were so incompetent not to check all entries. Any
cell can contain any value. That's flexibility, but it comes at a
price: it's up to the person writing the formulas that use user entries
to ensure those entries are valid. If that's different from database,
tough, this is just how spreadsheets work.

D) what happens when Susie; over in marketing-- wants to take vacation
days. She adds a column called 'vacation hours' and emails it to her boss.
Seems like a perfectly natural thing to do.

If the work*** were protected, she wouldn't be able to insert
anything. At that point she'd need to call he boss to ask how to
include vacation hours. Of course this raises the question whether time
sheets should include anything other than work hours, and if they
should, why wouldn't there already be entries for vacation hours?

Feable effort creating this straw man, but this may be all you can
dream up.

There are better ways-- email someone a form in Access; it gets
converted to a DAP (plain HTML); they enter all their data and
presto-chango-- I am ALREADY DONE.

And if they forward the e-mail to, say, their home e-mail account so
they can fill it out in the evening, would they be able to make entries
to your database from any machine with an internet connection? If so,
what prevents anyone else from feeding garbage into your database?

I have a free solution that is scalable and mulitple people can edit
their own data at the same time.

Really? Where's the url to download it so anyone can see whether this
claim is BS or not?

To the OP: Don't mind Aaron. He's right to suggest that Access may be
better than Excel for this provided you can access the authentication
lists via ODBC. But when it comes to the antispread*** ranting, he's
just angry because he's never been able to figure out how to use them.

.