excel to access problem

[rockonclimber]

i've been asked to convert an Excel spreadsheet to access. I used the wizard
to convert a simple test spreadsheet with no problems. The problem is that
when I click on a 'cell' (?) in the 'real' spreadsheet, lots of extra data
comes up - I appear to be linking to other sheets somehow. My test
spreadsheet was a simple one that I created from a flat file. I don't know
how to replicate the 'click on the box and see lot's more' functionality.

 

[Duane Hookom]

"lots of extra data" would need to be entered into related tables.

 

[Vincent Johns]

"lots of extra data" would need to be entered into related tables.

Indeed. I did this recently, and found Excel's "Auditing" tools to be
most helpful. There's even a predefined "Auditing" toolbar to make the
tools easy to access. For a given cell, you can easily identify other
cells depending on it, or those on which it depends. Any of these
dependencies you'll have to account for. You can import the linked data
into Access Tables, or in Access you can link a (virtual) Table to part
of your Excel spreadsheet.

If you're (unreasonably) lucky, it's (barely) possible that your
customer/boss will be satisfied with your importing only the values that
appear when you look at the spreadsheet. But if you want to replicate
the behavior, it's pretty likely that you'll need to write some VBA code
to do the equivalent thing in Access, depending on how tricky the Excel
code is. For example, it's possible that the cell you clicked on in
Excel runs some VBA code, and that could do just about anything. Good luck!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.