P
(PeteCresswell)
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?
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?