I'm writing out Excel files in XML format, and for the most part everything works as advertized. But there are a few things that are driving me to distraction.

Column based Formulas: I can't see a way to make a column be always calculated from a formula. Sure, I could write out the formula on each and every of the possible 65,536 rows, but ain't there a way to say that a column is just a function of the other values contained on this row. Something like:

A	B	C
hello	world	hello - world
foo	bar	foo - bar
foo	baz	foo - baz

where the third column is defined as =A&" - "&B.

New Rows: Ok, short of having column definitions, shouldn't it be possible to tell excel that all the formatting and validation information for any new rows added by the user should be the same as the last row's. Can't seem to figure out a way to do such.

Arrays in Formulas: Excel's formula language is noteworthy for cutting short of it's logical conclusions. Why can't a formula manipulate a range in a useful fashion. Ok, they are not bad for coming up with a single value, but what if you are trying to apply a range of values used for validation (e.g. drop down lists). Using the table above, what if I wanted to say that my dropdown was a function of "foo", such that the dropwdown shows "bar" and "baz"? Only way I can figure out is to have a helper set of cells that has a column for "hello" and a column for "foo" and then uses an Indirect reference to those cells. Why does the validation language have to be so crippled that it can not do the simplest of criteria formulas on a range of cells and columns?

And it was a standard part of Office (but alas, MS charges extra for it because they can).... Well, let's just say that Access also has a unique set of frustrations.

True, I am probably misapplying Excel for my own purposes. The frustration is not that it is incapable of doing what I want to do. Rather, it's close to the target but very verbose in the actual solutions - misdirection having to be applied all the way around.

Not using Office at all to generate the files, simply exporting a generated XML file that Excel understands. There's probably a way to get XML data into an Access Database, but I'd have to generate all the various things to make it a turnkey interface (i.e. we want to have it be automatically understood and then reimportable into the system (saving the file back as an xml document and then uploading it).

Anyhow, it's academic since excel is the Chosen One - much more pervasive for the account execs than is Access.

...and I probably wouldn't use it at any rate (can't afford the danger of someone thinking we're spreading macro virii). Not really trying to build a full excel interface. Just some simple precautions and protections - which can be easily defeated.