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?
Who is Participating?
Fabrice LambertConnect With a Mentor Fabrice 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.
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.