I have a large spreadsheet I use at work to manage a project I'm doing which isn't that user friendly when you need a quick overview of what's going on and how data is related, and was wondering the best way to proceed.
Each row is a location of an install, with the columns being various information, so you have general contact information having about half a dozen columns, pre install information taking up a dozen, equipment of the install taking up a few, invoice and PO information taking up a few, plus it also keeps track of the current status of that location since we deal with dozens and dozens simultaneously all at different stages, some with unique issues that need to be accounted for too.
I have other sheets which reference the first one to show only a few columns so I'm able to easily condence related information to fit on one screen, but this means these sheets are essentially read only, and there are limitations to how useful this is since if I re-sort the first page I need to re-setup these pages.
Problems keep happening, or take longer to solve than they should, because data isn't linking together well enough so we can't back trace things that happened months prior precisely and quickly, such like if a location have received two separate shipments of equipment on different dates and which PO and which invoice they were attached to.
I'm thinking an actual database is the way to go, and access is available to me but I was hoping for a bit of advice before I potentially go down the wrong path.
Perhaps, but I'd have to make a few more sheets for POs, Invoices and Deliveries, since you could have several invoices for a site, each PO will have several invoices, each site could have several Deliveries, or a delivery could be multiple Invoices of equipment from multiple POs.
Also, changing data in the master sheet isn't ideal because it's about 6 screens wide, so if you need columns from either side it's a pain.
I could break down the master sheet to be multiple sheets, but depending on what I'm doing certain information could go in multiple sheets, and when editing data I don't want to move between sheets to minimise mistakes.
Unless there's a way to reference data from another sheet, but it also able to update the original, but I'm thinking not?
Death's Head wrote:You said you are already referencing data on one sheet from another. I think your problem might be that you need to step back and design a proper layout rather than just trying to patch it up.
Considering I'm asking if it's worth rebuilding from the ground up in a new program, I'd like to think it was evident I'm not looking to just do a patch up here.
I'm just trying to figure out when i start over is it worth doing it again in excel, or if i should look into access.
To add a bit more information into the mix, ideally I'd like this to work over multiple projects, but it's not essential.
the issue with Access is patchy support/knowledge base i use Access for some basic stuff for some work i do and whenever i have a query and google it its extremely hard to find sensible input; half the responses are 'you can do this in excel lol'
<]:^D wrote:the issue with Access is patchy support/knowledge base i use Access for some basic stuff for some work i do and whenever i have a query and google it its extremely hard to find sensible input; half the responses are 'you can do this in excel lol'
That is one advantage of excel, you can Google stuff quite easily.
Rex Kramer wrote:What about local Web pages for data entry and reporting all linked to a mysql database?
I have no experience with mysql, what's the learning curve like?