Excel data consolidation question

I had a question -- I have a series of lists of users based on a userid

text string and their name, some of which are duplicates. What i've
done is import the lists into excel and then do a sort by userid.
Additionally, I made a note of which database they're each in, using a
different column for each of the four databases. I first copied all of
the entries from "users1" and then copied "users1" into every row as
column C. Next, I copied the "users2" list and for those, put "user2"
in column D...and so on up to users4.

Now, what I want to do is consolidate the entries, making sure there is

only ONE entry PER userid, but say if a user is in "user2" and "user3"
and thus has those entries in column D and E respectively, I want to
note both. Basically, I just want one row per userid and for each
userid, that row should include all "user(x)" column entries that they
exist in. Any ideas? I could also use access if that would be easier to

do this.


If it helps, I'm eventually going to compile all of these spreadsheets
(which are seperated by computer application they have access to...and
each of these "user(x)" subentries are different authentication
databases within that program)...and attempt to spit out a master list
that has one row per userid with all of the programs they have access
to (we wouldn't need to worry about the 'user(x)' subentries at that
point though, just if they exist in ANY of them).


Thanks much in advance or let me know if something isn't clear...


-Drew

[email protected] wrote...
>I had a question -- I have a series of lists of users based on a userid
>text string and their name, some of which are duplicates. What i've
>done is import the lists into excel and then do a sort by userid.
>Additionally, I made a note of which database they're each in, using a
>different column for each of the four databases. I first copied all of
>the entries from "users1" and then copied "users1" into every row as
>column C. Next, I copied the "users2" list and for those, put "user2"
>in column D...and so on up to users4.
>
>Now, what I want to do is consolidate the entries, making sure there is
>only ONE entry PER userid, but say if a user is in "user2" and "user3"
>and thus has those entries in column D and E respectively, I want to
>note both. Basically, I just want one row per userid and for each
>userid, that row should include all "user(x)" column entries that they
>exist in. Any ideas? I could also use access if that would be easier to
>do this.
....

So each list contains just user ID (UID) and name (UNAME) fields? And
the consolidated table's col C through F would also contain the UID?

You need to start with a master list of all UIDs, which means it'd be
easier to add a 3rd column to each table for the list ID (LID), so
user1 would have LID 1, user2 LID 2, etc. Then append the augmented
lists into a single long list with 3 columns. I'll call it SLL. Sort it
in ascending order first by UID then LID.

At that point you could use Advanced Filters to extract only one
instance of each UID and UNAME in SLL. Then you could add formulas to
the columns to the right of the distinct UID and UNAME to indicate the
UIDs' presence in each of the original lists. If the topmost UID and
UNAME were in X2:Y2, try the formulas

AD2:
=MATCH(X2,INDEX(SLL,0,1),0)

AE2:
=COUNTIF(INDEX(SLL,AD2,1):INDEX(SLL,AD2+3,1),X2)-1

Z2:
=IF(COUNTIF(INDEX(SLL,AD2,3):INDEX(SLL,AD2+AE2),COLUMNS($Z2:Z2)),X2,"")

Fill Z2 right into AA2:AC2, then select Z2:AE2 and either double click
on the fill handle or fill down into the same rows as the UIDs and
UNAMEs in cols X and Y.

If you could live with 1s or 0s indicating whether or not a particular
UID appears in a given table, you could use SLL to generate a pivot
table with UID and UNAME as the row variables and LID as the column
variable with operation COUNT.

Yes, each list contains the UID and UNAME fields, in addition to a few
others but they aren't important for this process. Column C through F
on the consolidated table just note that UID/UNAME exists on the
respective source (Column C being user_ss4 and Column D being
EA_list_7, for example). It can really be anything. What I've been
doing is pasting each of the lists, leaving a space or two between each
list. The first list of UID/UNAMEs I'd put "user_ss4" in column C, then
for the next list i'd put "EA_list_7" in column D, etc...just so if I
eventually pulled them all together, there wouldn't be more than one
item in each column. I'll try out your suggestions now, just thought
I'd note this.

Otherwise, if Access would work better, I have no aversion to using it
at all.

Harlan Grove wrote:

> So each list contains just user ID (UID) and name (UNAME) fields? And
> the consolidated table's col C through F would also contain the UID?
>
> You need to start with a master list of all UIDs, which means it'd be
> easier to add a 3rd column to each table for the list ID (LID), so
> user1 would have LID 1, user2 LID 2, etc. Then append the augmented
> lists into a single long list with 3 columns. I'll call it SLL. Sort it
> in ascending order first by UID then LID.
>
> At that point you could use Advanced Filters to extract only one
> instance of each UID and UNAME in SLL. Then you could add formulas to
> the columns to the right of the distinct UID and UNAME to indicate the
> UIDs' presence in each of the original lists. If the topmost UID and
> UNAME were in X2:Y2, try the formulas
>
> AD2:
> =MATCH(X2,INDEX(SLL,0,1),0)
>
> AE2:
> =COUNTIF(INDEX(SLL,AD2,1):INDEX(SLL,AD2+3,1),X2)-1
>
> Z2:
> =IF(COUNTIF(INDEX(SLL,AD2,3):INDEX(SLL,AD2+AE2),COLUMNS($Z2:Z2)),X2,"")
>
> Fill Z2 right into AA2:AC2, then select Z2:AE2 and either double click
> on the fill handle or fill down into the same rows as the UIDs and
> UNAMEs in cols X and Y.
>
> If you could live with 1s or 0s indicating whether or not a particular
> UID appears in a given table, you could use SLL to generate a pivot
> table with UID and UNAME as the row variables and LID as the column
> variable with operation COUNT.

Harlan;

So you're going to do this

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


For each of a trillion different cells.

And then you'll open up each spreadsheet by hand and if it has a 1 in
there; then you'll know that you've got to change this value by hand?

Are you kidding me??

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

it's just functionality that is built into Access.

File, SendTo

if worst comes to worse; you can save your form as a Data Access Page
and then email it around.

I've built quite a few complex 'Excel Replacement' solutions using this
type of technology.

but it looks just like a webpage; it is a simple webpage.
it uses these components called 'office web components'

When I say 'components' think of something similiar to Adobe Acrobat
Reader; or Flash.. all it consists of is a 'Object Tag' in HTML.

and these components are what make Excel completely and utterly
OBSOLETE.

you know when you save a spreadsheet as HTML and 'add interactivity'?
that is what I am talking about.

It isn't a piece of Excel-- these are primarily components that are
best created using Microsoft Access (or something like dreamweaver for
example).

those are the components that I'm referring to. I use those ALL DAY
LONG; EVERY DAY.
and they provide things like:

a) drilldown - the ability to have a drilldown effect in a pivotTable
b) displaying a field; but keep it collapsed-- so that drilldown is
easy
c) the ability to create custom fields INSIDE the pivotTable (you have
to create custom formulas OUTSIDE of a pivotTable)
d) the ability to have 250,000 rows in a 'spreadsheet'


Here is a basic page to help you to get your feet wet with Access
http://www.bcschools.net/staff/AccessHelp.htm

Here is a starter page that describes some funcitonality that is found
within Data Access Pages
http://office.microsoft.com/en-us/as...526501033.aspx

Information about emailing Data Access Pages
http://office.microsoft.com/en-us/as...890051033.aspx

you don't need 'Access' on your machine in order to fill out these
forms. All you need is a valid office license (2002 or 2003) and then
one of these products:

a) Access
b) Excel
c) Word
d) Outlook

I think that any of those 4 products counts as a license to use Office
Web Components.

-Aaron




Harlan Grove wrote:
> [email protected] wrote...
> ...
> >how does the manager import 30 spreadsheets and consolidate numbers out
> >of all 30 workbooks?
>
> http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting, he's
> just angry because he's never been able to figure out how to use them.

Harlan

and for the record? the thing that makes this SECURE?

it's called 'windows authentication'

you can use SQL authenticaiton if you would prefer; you can even encrpt
the HTML so that it isn't readable in plain-text.

it does allow for offline-usage.. that would require something on the
clientside to push the XML buffer back to the server.

-Aaron


Harlan Grove wrote:
> [email protected] wrote...
> ...
> >how does the manager import 30 spreadsheets and consolidate numbers out
> >of all 30 workbooks?
>
> http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting, he's
> just angry because he's never been able to figure out how to use them.

Aaron -- can you tell me how I was trying to do in access? You appear
to actually know what you're doing, and if it's so easy and you're so
proficient, i'm actually interested in seeing how you'd do this.

[email protected] wrote:
> Harlan
>
> and for the record? the thing that makes this SECURE?
>
> it's called 'windows authentication'
>
> you can use SQL authenticaiton if you would prefer; you can even encrpt
> the HTML so that it isn't readable in plain-text.
>
> it does allow for offline-usage.. that would require something on the
> clientside to push the XML buffer back to the server.
>
> -Aaron
>
>
> Harlan Grove wrote:
> > [email protected] wrote...
> > ...
> > >how does the manager import 30 spreadsheets and consolidate numbers out
> > >of all 30 workbooks?
> >
> > http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting, he's
> > just angry because he's never been able to figure out how to use them.

Aaron

Three things about OWC

1) They are ActiveX and now blocked by default in WinXP SP2
2) They are deprecated in O12
3) Any web presentation is better hosted on the server (asp, asp.net, php,
etc) and presented in some of the new controls which implement XHTML, etc
which give rich user views, with no security warnings and server security,
without *any* need for client installed controls

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


<[email protected]> wrote in message
news:[email protected]...
>
> Harlan;
>
> So you're going to do this
>
> =COUNT(1/(ABS(YEAR(DateEntry)-1960)<=60))
>
>
> For each of a trillion different cells.
>
> And then you'll open up each spreadsheet by hand and if it has a 1 in
> there; then you'll know that you've got to change this value by hand?
>
> Are you kidding me??
>
> Re:
>> Really? Where's the url to download it so anyone can see whether this
>> claim is BS or not?
>
> it's just functionality that is built into Access.
>
> File, SendTo
>
> if worst comes to worse; you can save your form as a Data Access Page
> and then email it around.
>
> I've built quite a few complex 'Excel Replacement' solutions using this
> type of technology.
>
> but it looks just like a webpage; it is a simple webpage.
> it uses these components called 'office web components'
>
> When I say 'components' think of something similiar to Adobe Acrobat
> Reader; or Flash.. all it consists of is a 'Object Tag' in HTML.
>
> and these components are what make Excel completely and utterly
> OBSOLETE.
>
> you know when you save a spreadsheet as HTML and 'add interactivity'?
> that is what I am talking about.
>
> It isn't a piece of Excel-- these are primarily components that are
> best created using Microsoft Access (or something like dreamweaver for
> example).
>
> those are the components that I'm referring to. I use those ALL DAY
> LONG; EVERY DAY.
> and they provide things like:
>
> a) drilldown - the ability to have a drilldown effect in a pivotTable
> b) displaying a field; but keep it collapsed-- so that drilldown is
> easy
> c) the ability to create custom fields INSIDE the pivotTable (you have
> to create custom formulas OUTSIDE of a pivotTable)
> d) the ability to have 250,000 rows in a 'spreadsheet'
>
>
> Here is a basic page to help you to get your feet wet with Access
> http://www.bcschools.net/staff/AccessHelp.htm
>
> Here is a starter page that describes some funcitonality that is found
> within Data Access Pages
> http://office.microsoft.com/en-us/as...526501033.aspx
>
> Information about emailing Data Access Pages
> http://office.microsoft.com/en-us/as...890051033.aspx
>
> you don't need 'Access' on your machine in order to fill out these
> forms. All you need is a valid office license (2002 or 2003) and then
> one of these products:
>
> a) Access
> b) Excel
> c) Word
> d) Outlook
>
> I think that any of those 4 products counts as a license to use Office
> Web Components.
>
> -Aaron
>
>
>
>
> Harlan Grove wrote:
>> [email protected] wrote...
>> ...
>> >how does the manager import 30 spreadsheets and consolidate numbers out
>> >of all 30 workbooks?
>>
>> http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting, he's
>> just angry because he's never been able to figure out how to use them.
>

Nick;

you are such a ******* liar.
they're not blocked in XP SP2.

they're not depecrated in Office 12. Office 12 hasn't shipped yet
fucktard so go screw yourself.

'any web presentation is better hosted on the server'

oh i'm so glad that you are the SINGLE person that has EVERY POSSIBLE
understanding of how it's soooooo inefficient to run things on the
clientside.

I mean; if everything should run on the serverside; why is google and
yahoo and microsoft making powerful clientside AJAX applications?

OWC are the same thing as AJAX for all practical reasons.... a richer
client experience.
there isn't an interface in the world; anywhere-- that can compete with
OWC pivotTables for price, performance, portability and did i mention
PRICE?

your simple server-side pages are for losers.

Aren't you tired of posting back to the webserver a dozen times on
every page?

I personally hate how when you're typing something in windows live
mail; it's talking to the server and trying to auto-complete the name
of the contact that you're typing.

it's like the worst; slowest design EVER.

yeah.. 'everything should run on the server side'

kids.. ****

so uh.. where are flash add-ins going to run then; kid??

-Aaron



Nick Hodge wrote:
> Aaron
>
> Three things about OWC
>
> 1) They are ActiveX and now blocked by default in WinXP SP2
> 2) They are deprecated in O12
> 3) Any web presentation is better hosted on the server (asp, asp.net, php,
> etc) and presented in some of the new controls which implement XHTML, etc
> which give rich user views, with no security warnings and server security,
> without *any* need for client installed controls
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> >
> > Harlan;
> >
> > So you're going to do this
> >
> > =COUNT(1/(ABS(YEAR(DateEntry)-1960)<=60))
> >
> >
> > For each of a trillion different cells.
> >
> > And then you'll open up each spreadsheet by hand and if it has a 1 in
> > there; then you'll know that you've got to change this value by hand?
> >
> > Are you kidding me??
> >
> > Re:
> >> Really? Where's the url to download it so anyone can see whether this
> >> claim is BS or not?
> >
> > it's just functionality that is built into Access.
> >
> > File, SendTo
> >
> > if worst comes to worse; you can save your form as a Data Access Page
> > and then email it around.
> >
> > I've built quite a few complex 'Excel Replacement' solutions using this
> > type of technology.
> >
> > but it looks just like a webpage; it is a simple webpage.
> > it uses these components called 'office web components'
> >
> > When I say 'components' think of something similiar to Adobe Acrobat
> > Reader; or Flash.. all it consists of is a 'Object Tag' in HTML.
> >
> > and these components are what make Excel completely and utterly
> > OBSOLETE.
> >
> > you know when you save a spreadsheet as HTML and 'add interactivity'?
> > that is what I am talking about.
> >
> > It isn't a piece of Excel-- these are primarily components that are
> > best created using Microsoft Access (or something like dreamweaver for
> > example).
> >
> > those are the components that I'm referring to. I use those ALL DAY
> > LONG; EVERY DAY.
> > and they provide things like:
> >
> > a) drilldown - the ability to have a drilldown effect in a pivotTable
> > b) displaying a field; but keep it collapsed-- so that drilldown is
> > easy
> > c) the ability to create custom fields INSIDE the pivotTable (you have
> > to create custom formulas OUTSIDE of a pivotTable)
> > d) the ability to have 250,000 rows in a 'spreadsheet'
> >
> >
> > Here is a basic page to help you to get your feet wet with Access
> > http://www.bcschools.net/staff/AccessHelp.htm
> >
> > Here is a starter page that describes some funcitonality that is found
> > within Data Access Pages
> > http://office.microsoft.com/en-us/as...526501033.aspx
> >
> > Information about emailing Data Access Pages
> > http://office.microsoft.com/en-us/as...890051033.aspx
> >
> > you don't need 'Access' on your machine in order to fill out these
> > forms. All you need is a valid office license (2002 or 2003) and then
> > one of these products:
> >
> > a) Access
> > b) Excel
> > c) Word
> > d) Outlook
> >
> > I think that any of those 4 products counts as a license to use Office
> > Web Components.
> >
> > -Aaron
> >
> >
> >
> >
> > Harlan Grove wrote:
> >> [email protected] wrote...
> >> ...
> >> >how does the manager import 30 spreadsheets and consolidate numbers out
> >> >of all 30 workbooks?
> >>
> >> http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting, he's
> >> just angry because he's never been able to figure out how to use them.
> >

and for the record; it ISN"T a client installed control-- since it
comes with OFFICE FUCKTARD

and even if someone didn't have it installed; it's easy to prompt
people-- a single line of plain HTML says 'click here to install' or
the path to the CAB files on your webserver; it's pretty much
self-installing

-Aaron



Nick Hodge wrote:
> Aaron
>
> Three things about OWC
>
> 1) They are ActiveX and now blocked by default in WinXP SP2
> 2) They are deprecated in O12
> 3) Any web presentation is better hosted on the server (asp, asp.net, php,
> etc) and presented in some of the new controls which implement XHTML, etc
> which give rich user views, with no security warnings and server security,
> without *any* need for client installed controls
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> >
> > Harlan;
> >
> > So you're going to do this
> >
> > =COUNT(1/(ABS(YEAR(DateEntry)-1960)<=60))
> >
> >
> > For each of a trillion different cells.
> >
> > And then you'll open up each spreadsheet by hand and if it has a 1 in
> > there; then you'll know that you've got to change this value by hand?
> >
> > Are you kidding me??
> >
> > Re:
> >> Really? Where's the url to download it so anyone can see whether this
> >> claim is BS or not?
> >
> > it's just functionality that is built into Access.
> >
> > File, SendTo
> >
> > if worst comes to worse; you can save your form as a Data Access Page
> > and then email it around.
> >
> > I've built quite a few complex 'Excel Replacement' solutions using this
> > type of technology.
> >
> > but it looks just like a webpage; it is a simple webpage.
> > it uses these components called 'office web components'
> >
> > When I say 'components' think of something similiar to Adobe Acrobat
> > Reader; or Flash.. all it consists of is a 'Object Tag' in HTML.
> >
> > and these components are what make Excel completely and utterly
> > OBSOLETE.
> >
> > you know when you save a spreadsheet as HTML and 'add interactivity'?
> > that is what I am talking about.
> >
> > It isn't a piece of Excel-- these are primarily components that are
> > best created using Microsoft Access (or something like dreamweaver for
> > example).
> >
> > those are the components that I'm referring to. I use those ALL DAY
> > LONG; EVERY DAY.
> > and they provide things like:
> >
> > a) drilldown - the ability to have a drilldown effect in a pivotTable
> > b) displaying a field; but keep it collapsed-- so that drilldown is
> > easy
> > c) the ability to create custom fields INSIDE the pivotTable (you have
> > to create custom formulas OUTSIDE of a pivotTable)
> > d) the ability to have 250,000 rows in a 'spreadsheet'
> >
> >
> > Here is a basic page to help you to get your feet wet with Access
> > http://www.bcschools.net/staff/AccessHelp.htm
> >
> > Here is a starter page that describes some funcitonality that is found
> > within Data Access Pages
> > http://office.microsoft.com/en-us/as...526501033.aspx
> >
> > Information about emailing Data Access Pages
> > http://office.microsoft.com/en-us/as...890051033.aspx
> >
> > you don't need 'Access' on your machine in order to fill out these
> > forms. All you need is a valid office license (2002 or 2003) and then
> > one of these products:
> >
> > a) Access
> > b) Excel
> > c) Word
> > d) Outlook
> >
> > I think that any of those 4 products counts as a license to use Office
> > Web Components.
> >
> > -Aaron
> >
> >
> >
> >
> > Harlan Grove wrote:
> >> [email protected] wrote...
> >> ...
> >> >how does the manager import 30 spreadsheets and consolidate numbers out
> >> >of all 30 workbooks?
> >>
> >> http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting, he's
> >> just angry because he's never been able to figure out how to use them.
> >

1) open MS Access and create a new application / database with a mdb
extension if you're a newbie
2) Tools, Options, 'Pages' set up a UNC path (to a server share; or
mapped network drive or something) for the 'default connection file'
3) right-click import your spreadsheet data into Access.
4) fix some of the field names; etc to not have spaces; etc
5) select the table on which you want people to enter data
6) make a view that filters WHERE USER = SUSER_SNAME()
7) select the view (oir query) in the database window
8) go INSERT, PAGE and fill out the data access page wizard. Yes, Add,
Next, next, Add, Yes; etc
9) hit file, save in order to Save the DAP to the Access file that
you're working with.
10) it will also prompt you where to save the plain HTML file.
11) save it to a UNC path or to your C drive or something
12) right click in Windows and go 'Send-To (mail-recipient)'

rinse and repeat.

-Aaron



atomiks wrote:
> Aaron -- can you tell me how I was trying to do in access? You appear
> to actually know what you're doing, and if it's so easy and you're so
> proficient, i'm actually interested in seeing how you'd do this.
>
> [email protected] wrote:
> > Harlan
> >
> > and for the record? the thing that makes this SECURE?
> >
> > it's called 'windows authentication'
> >
> > you can use SQL authenticaiton if you would prefer; you can even encrpt
> > the HTML so that it isn't readable in plain-text.
> >
> > it does allow for offline-usage.. that would require something on the
> > clientside to push the XML buffer back to the server.
> >
> > -Aaron
> >
> >
> > Harlan Grove wrote:
> > > [email protected] wrote...
> > > ...
> > > >how does the manager import 30 spreadsheets and consolidate numbers out
> > > >of all 30 workbooks?
> > >
> > > http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting, he's
> > > just angry because he's never been able to figure out how to use them.

Aaron

For so long as your route to make your point is personal abuse to an
unsatisfactory level, I'll withdraw, not because your aggression intimidates
me, but because my intelligence and care for others stops me from going to
your level and I think everyone now see's the 'cut of your cloth'.

Suffice to say, you will notice that Office 12 will not ship with
OWC...fact, ActiveX controls will 'at best' fire the warning bar at the top
in IE. The comment on server side and client side controls ends up a little
like our ongoing 'discussions' on Excel/Access, each has there use and the
'cross-over' may be a little grey.

As I said....from my standpoint, whatever your reply on this....End!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


<[email protected]> wrote in message
news:[email protected]...
> Nick;
>
> you are such a ******* liar.
> they're not blocked in XP SP2.
>
> they're not depecrated in Office 12. Office 12 hasn't shipped yet
> fucktard so go screw yourself.
>
> 'any web presentation is better hosted on the server'
>
> oh i'm so glad that you are the SINGLE person that has EVERY POSSIBLE
> understanding of how it's soooooo inefficient to run things on the
> clientside.
>
> I mean; if everything should run on the serverside; why is google and
> yahoo and microsoft making powerful clientside AJAX applications?
>
> OWC are the same thing as AJAX for all practical reasons.... a richer
> client experience.
> there isn't an interface in the world; anywhere-- that can compete with
> OWC pivotTables for price, performance, portability and did i mention
> PRICE?
>
> your simple server-side pages are for losers.
>
> Aren't you tired of posting back to the webserver a dozen times on
> every page?
>
> I personally hate how when you're typing something in windows live
> mail; it's talking to the server and trying to auto-complete the name
> of the contact that you're typing.
>
> it's like the worst; slowest design EVER.
>
> yeah.. 'everything should run on the server side'
>
> kids.. ****
>
> so uh.. where are flash add-ins going to run then; kid??
>
> -Aaron
>
>
>
> Nick Hodge wrote:
>> Aaron
>>
>> Three things about OWC
>>
>> 1) They are ActiveX and now blocked by default in WinXP SP2
>> 2) They are deprecated in O12
>> 3) Any web presentation is better hosted on the server (asp, asp.net,
>> php,
>> etc) and presented in some of the new controls which implement XHTML, etc
>> which give rich user views, with no security warnings and server
>> security,
>> without *any* need for client installed controls
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> www.nickhodge.co.uk
>> [email protected]HIS
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> > Harlan;
>> >
>> > So you're going to do this
>> >
>> > =COUNT(1/(ABS(YEAR(DateEntry)-1960)<=60))
>> >
>> >
>> > For each of a trillion different cells.
>> >
>> > And then you'll open up each spreadsheet by hand and if it has a 1 in
>> > there; then you'll know that you've got to change this value by hand?
>> >
>> > Are you kidding me??
>> >
>> > Re:
>> >> Really? Where's the url to download it so anyone can see whether this
>> >> claim is BS or not?
>> >
>> > it's just functionality that is built into Access.
>> >
>> > File, SendTo
>> >
>> > if worst comes to worse; you can save your form as a Data Access Page
>> > and then email it around.
>> >
>> > I've built quite a few complex 'Excel Replacement' solutions using this
>> > type of technology.
>> >
>> > but it looks just like a webpage; it is a simple webpage.
>> > it uses these components called 'office web components'
>> >
>> > When I say 'components' think of something similiar to Adobe Acrobat
>> > Reader; or Flash.. all it consists of is a 'Object Tag' in HTML.
>> >
>> > and these components are what make Excel completely and utterly
>> > OBSOLETE.
>> >
>> > you know when you save a spreadsheet as HTML and 'add interactivity'?
>> > that is what I am talking about.
>> >
>> > It isn't a piece of Excel-- these are primarily components that are
>> > best created using Microsoft Access (or something like dreamweaver for
>> > example).
>> >
>> > those are the components that I'm referring to. I use those ALL DAY
>> > LONG; EVERY DAY.
>> > and they provide things like:
>> >
>> > a) drilldown - the ability to have a drilldown effect in a pivotTable
>> > b) displaying a field; but keep it collapsed-- so that drilldown is
>> > easy
>> > c) the ability to create custom fields INSIDE the pivotTable (you have
>> > to create custom formulas OUTSIDE of a pivotTable)
>> > d) the ability to have 250,000 rows in a 'spreadsheet'
>> >
>> >
>> > Here is a basic page to help you to get your feet wet with Access
>> > http://www.bcschools.net/staff/AccessHelp.htm
>> >
>> > Here is a starter page that describes some funcitonality that is found
>> > within Data Access Pages
>> > http://office.microsoft.com/en-us/as...526501033.aspx
>> >
>> > Information about emailing Data Access Pages
>> > http://office.microsoft.com/en-us/as...890051033.aspx
>> >
>> > you don't need 'Access' on your machine in order to fill out these
>> > forms. All you need is a valid office license (2002 or 2003) and then
>> > one of these products:
>> >
>> > a) Access
>> > b) Excel
>> > c) Word
>> > d) Outlook
>> >
>> > I think that any of those 4 products counts as a license to use Office
>> > Web Components.
>> >
>> > -Aaron
>> >
>> >
>> >
>> >
>> > Harlan Grove wrote:
>> >> [email protected] wrote...
>> >> ...
>> >> >how does the manager import 30 spreadsheets and consolidate numbers
>> >> >out
>> >> >of all 30 workbooks?
>> >>
>> >> http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting,
>> >> he's
>> >> just angry because he's never been able to figure out how to use them.
>> >
>

Nick;

I just disagree with you trying to spread lies

OWC isn't 'going away' it will be supported; according to what MS has
told us-- until 2011-2013.

That is 'long enough' for most projects.

I dont know what Office 2007 will hold. I don't really care.

All I know is that there ISN"T a better option today for displaying and
editing data.

PivotTables; Spreadsheet and Data Access Pages-- .NET can .NET compete
with this stack.

-Aaron


Nick Hodge wrote:
> Aaron
>
> For so long as your route to make your point is personal abuse to an
> unsatisfactory level, I'll withdraw, not because your aggression intimidates
> me, but because my intelligence and care for others stops me from going to
> your level and I think everyone now see's the 'cut of your cloth'.
>
> Suffice to say, you will notice that Office 12 will not ship with
> OWC...fact, ActiveX controls will 'at best' fire the warning bar at the top
> in IE. The comment on server side and client side controls ends up a little
> like our ongoing 'discussions' on Excel/Access, each has there use and the
> 'cross-over' may be a little grey.
>
> As I said....from my standpoint, whatever your reply on this....End!
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Nick;
> >
> > you are such a ******* liar.
> > they're not blocked in XP SP2.
> >
> > they're not depecrated in Office 12. Office 12 hasn't shipped yet
> > fucktard so go screw yourself.
> >
> > 'any web presentation is better hosted on the server'
> >
> > oh i'm so glad that you are the SINGLE person that has EVERY POSSIBLE
> > understanding of how it's soooooo inefficient to run things on the
> > clientside.
> >
> > I mean; if everything should run on the serverside; why is google and
> > yahoo and microsoft making powerful clientside AJAX applications?
> >
> > OWC are the same thing as AJAX for all practical reasons.... a richer
> > client experience.
> > there isn't an interface in the world; anywhere-- that can compete with
> > OWC pivotTables for price, performance, portability and did i mention
> > PRICE?
> >
> > your simple server-side pages are for losers.
> >
> > Aren't you tired of posting back to the webserver a dozen times on
> > every page?
> >
> > I personally hate how when you're typing something in windows live
> > mail; it's talking to the server and trying to auto-complete the name
> > of the contact that you're typing.
> >
> > it's like the worst; slowest design EVER.
> >
> > yeah.. 'everything should run on the server side'
> >
> > kids.. ****
> >
> > so uh.. where are flash add-ins going to run then; kid??
> >
> > -Aaron
> >
> >
> >
> > Nick Hodge wrote:
> >> Aaron
> >>
> >> Three things about OWC
> >>
> >> 1) They are ActiveX and now blocked by default in WinXP SP2
> >> 2) They are deprecated in O12
> >> 3) Any web presentation is better hosted on the server (asp, asp.net,
> >> php,
> >> etc) and presented in some of the new controls which implement XHTML, etc
> >> which give rich user views, with no security warnings and server
> >> security,
> >> without *any* need for client installed controls
> >>
> >> --
> >> HTH
> >> Nick Hodge
> >> Microsoft MVP - Excel
> >> Southampton, England
> >> www.nickhodge.co.uk
> >> [email protected]HIS
> >>
> >>
> >> <[email protected]> wrote in message
> >> news:[email protected]...
> >> >
> >> > Harlan;
> >> >
> >> > So you're going to do this
> >> >
> >> > =COUNT(1/(ABS(YEAR(DateEntry)-1960)<=60))
> >> >
> >> >
> >> > For each of a trillion different cells.
> >> >
> >> > And then you'll open up each spreadsheet by hand and if it has a 1 in
> >> > there; then you'll know that you've got to change this value by hand?
> >> >
> >> > Are you kidding me??
> >> >
> >> > Re:
> >> >> Really? Where's the url to download it so anyone can see whether this
> >> >> claim is BS or not?
> >> >
> >> > it's just functionality that is built into Access.
> >> >
> >> > File, SendTo
> >> >
> >> > if worst comes to worse; you can save your form as a Data Access Page
> >> > and then email it around.
> >> >
> >> > I've built quite a few complex 'Excel Replacement' solutions using this
> >> > type of technology.
> >> >
> >> > but it looks just like a webpage; it is a simple webpage.
> >> > it uses these components called 'office web components'
> >> >
> >> > When I say 'components' think of something similiar to Adobe Acrobat
> >> > Reader; or Flash.. all it consists of is a 'Object Tag' in HTML.
> >> >
> >> > and these components are what make Excel completely and utterly
> >> > OBSOLETE.
> >> >
> >> > you know when you save a spreadsheet as HTML and 'add interactivity'?
> >> > that is what I am talking about.
> >> >
> >> > It isn't a piece of Excel-- these are primarily components that are
> >> > best created using Microsoft Access (or something like dreamweaver for
> >> > example).
> >> >
> >> > those are the components that I'm referring to. I use those ALL DAY
> >> > LONG; EVERY DAY.
> >> > and they provide things like:
> >> >
> >> > a) drilldown - the ability to have a drilldown effect in a pivotTable
> >> > b) displaying a field; but keep it collapsed-- so that drilldown is
> >> > easy
> >> > c) the ability to create custom fields INSIDE the pivotTable (you have
> >> > to create custom formulas OUTSIDE of a pivotTable)
> >> > d) the ability to have 250,000 rows in a 'spreadsheet'
> >> >
> >> >
> >> > Here is a basic page to help you to get your feet wet with Access
> >> > http://www.bcschools.net/staff/AccessHelp.htm
> >> >
> >> > Here is a starter page that describes some funcitonality that is found
> >> > within Data Access Pages
> >> > http://office.microsoft.com/en-us/as...526501033.aspx
> >> >
> >> > Information about emailing Data Access Pages
> >> > http://office.microsoft.com/en-us/as...890051033.aspx
> >> >
> >> > you don't need 'Access' on your machine in order to fill out these
> >> > forms. All you need is a valid office license (2002 or 2003) and then
> >> > one of these products:
> >> >
> >> > a) Access
> >> > b) Excel
> >> > c) Word
> >> > d) Outlook
> >> >
> >> > I think that any of those 4 products counts as a license to use Office
> >> > Web Components.
> >> >
> >> > -Aaron
> >> >
> >> >
> >> >
> >> >
> >> > Harlan Grove wrote:
> >> >> [email protected] wrote...
> >> >> ...
> >> >> >how does the manager import 30 spreadsheets and consolidate numbers
> >> >> >out
> >> >> >of all 30 workbooks?
> >> >>
> >> >> http://www.google.com/groups?selm=hk...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 spreadsheet 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 worksheet 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 antispreadsheet ranting,
> >> >> he's
> >> >> just angry because he's never been able to figure out how to use them.
> >> >
> >

Aaron is like the man behind the curtain in the Wizard of Oz....."pay no attention to what's behind the curtain......!" He hides behind his curtain of not-thought-out rantings and spouts gibberish. He obviously has no idea of what he's talking about.... ever notice how he gives meaningless responses and then expects a person to accept his drivel?? How about his mumblings if you disagree with him, not to mention the names he insults people with.

Wonder if Microsoft had a party when he "left?"