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?