I'm creating a spreadsheet from an MS Access app.

It lists various bond holdings, properties of said bonds, and
amounts held in various funds.

So far so good.....

But now I need to add a new section to the sheet: A "what if"
section.

The area looks just like the bond holding list above it and has,
say, 20 rows - except that instead of a bond name in column one
of each row, there will be a drop down. User pops the drop
down, selects a bond name, and the row is populated with
properties of that bond.

I guess the good-right-and-holy path is to open up a DAO
recordset behind the combo box and link the original MS Access
back end.

But I want to avoid that in order to make the spreadsheet
self-contained and independent of the app.


Being short on Excel expertise, my kneejerk reaction is to create
and populate an invisible worksheet whose columns mirror the
fields I need from the back end's bond table. Then, in the
combo box's AfterUpdate or whatever fires after the user chooses
a bond name, I somehow do a lookup on the invisible sheet for
that name and retrieve the other props as needed.

Is this the right way to do it?

Or is there some more elegant/efficient method (besides linking
to a separate .MDB)? Maybe some way to stash a JET DAO table
inside of an Excel spreadsheet document?
--
PeteCresswell

To my knowledge, Excel does not have the functionality to store relational
data "within the workbook" so your approach of storing the bond data in a
seperate worksheet would be the only way to have an independant workbook
with all data contained in the xls file with no external links.

The downside to this (as I am sure you already know) is that the data in the
xls file will not be synchronised with the "live" data in the database.
This may or may not be a problem for you, but it's something you should
consider.

MH

"(PeteCresswell)" wrote in message
...
I'm creating a spreadsheet from an MS Access app.

It lists various bond holdings, properties of said bonds, and
amounts held in various funds.

So far so good.....

But now I need to add a new section to the sheet: A "what if"
section.

The area looks just like the bond holding list above it and has,
say, 20 rows - except that instead of a bond name in column one
of each row, there will be a drop down. User pops the drop
down, selects a bond name, and the row is populated with
properties of that bond.

I guess the good-right-and-holy path is to open up a DAO
recordset behind the combo box and link the original MS Access
back end.

But I want to avoid that in order to make the spreadsheet
self-contained and independent of the app.


Being short on Excel expertise, my kneejerk reaction is to create
and populate an invisible worksheet whose columns mirror the
fields I need from the back end's bond table. Then, in the
combo box's AfterUpdate or whatever fires after the user chooses
a bond name, I somehow do a lookup on the invisible sheet for
that name and retrieve the other props as needed.

Is this the right way to do it?

Or is there some more elegant/efficient method (besides linking
to a separate .MDB)? Maybe some way to stash a JET DAO table
inside of an Excel spreadsheet document?
--
PeteCresswell