Best way to structure recordset in Excel

I have been working with MS Access for years where it is quite easy to manage which fields are visible on each form but I now have to undertake a project using MS Excel and I am after a bit of advice as to how to best structure the spreadsheet(s).

Actually the overall structure is fairly simple if I could just use a single spreadsheet with each row representing a job and columns representing details about the job (such as address details, customer, order details, delivery details, invoicing details, and  contractor invoicing details). A single job is complete when all these entries have been completed. I could then use macros to hide/show desired columns, depending on what function the user is doing (i.e. Order entry, scheduling, invoicing or contractor payments.

However I have a few restrictions:
1. I cannot use a macro enabled workbook
2. It would work more efficiently if I could have different spreadsheets for each function (Order entry, scheduling, invoicing, contractor payments)

I can create a key field easy enough but is there a way of easily linking multiple spreadsheets together to maintain integrity and manage the whole process, or do I need to use vLookups or Match/Index lookups to link tables together? What is the usual/recommended approach when working with databases on Excel?
LVL 1
Rob4077Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Even if excel files can be read by ODBC drivers, it is by no mean a database.

Without macros, guess you can use validation rules, it is in the data tab.
Also, checkbox and/or combobox can be handy.
0
Rob4077Author Commented:
Thanks Fabrice. I realise a spreadsheet is not what would normally be called a database but in this case it is easy to process the information by treating the spreadsheet as a flat file with each row being a complete record. I was just hoping that I could somehow spread each row across several spreadsheets (so that each sheet presents well for the functions it is used for) and easily link them together with a common key (like I can with SQL in MS Access relating tables together). As I thought, your comment suggests this is not possible in Excel.
0
Fabrice LambertFabrice LambertCommented:
As I said, it is more or less doable, with well designed spreadsheets and data validation rules you can restrict data entered by users (allowed values will show up in a dropdown), this is like enforcing foreign key constraints.

As for primary keys constraint or unique constraints, I'm not sure it is doable.

On the other hand, dynamically display / hide columns without macro isn't doable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.