database selection

[WinterDeere]

Is the spreadsheet constantly breaking the biggest problem?

It's definitely an annoyance. I can usually fix it with just a few minutes, but it can really frustrate other users, and hose things up when they try fixing on their own.

The reality is there are no problems, until Excel's built-in intelligence "fixes" them. Things like named ranges inside of tables, which work fine when the document is not opened, get automatically reformated to relative ranges inside of a named table... which don't work when the referenced file is not opened. Totally stupid stuff, that never caused any problem on older versions of Excel, but now routinely breaking themselves on more recent versions.

Getting the whole system away from Excel is a foregone conclusion. I'm just tired of finding ways to make it work beyond its intended purpose.

 

[quicksandfarmer]

I've used Google sheets for projects like that. They allow multiple people to view/edit the sheet at the same time and you can set permissions on sheets. It would be an evolutionary step from Excel.

They also have a scripting language that is very useful.

 

[riptides]

It's definitely an annoyance. I can usually fix it with just a few minutes, but it can really frustrate other users, and hose things up when they try fixing on their own.

The reality is there are no problems, until Excel's built-in intelligence "fixes" them. Things like named ranges inside of tables, which work fine when the document is not opened, get automatically reformated to relative ranges inside of a named table... which don't work when the referenced file is not opened. Totally stupid stuff, that never caused any problem on older versions of Excel, but now routinely breaking themselves on more recent versions.

Getting the whole system away from Excel is a foregone conclusion. I'm just tired of finding ways to make it work beyond its intended purpose.

I think you really should take a harder look at databases for this.

You need separation of duties and data access. On top of this you need canned reporting.

You should spend your time building, not fixing. Better for all involved.

Good luck.

 

[WinterDeere]

I think you really should take a harder look at databases for this.

MS Access is a database.

Or are you referring to commercial ERP software?

 

[riptides]

MS Access is a database.

Or are you referring to commercial ERP software?

No. Just making a hard sell on upsizing to Access for you.

Who does the data input and/or query/macro builds in all the spreadsheets? Exactly what are they putting in? Are they updating existing rows and columns or adding data?

 

[WinterDeere]

Until now, it has been entirely me, which is why Excel was a great starting point for this system. But now work has grown to where I'm having to bring in contractors and outsource, and I'm getting tired of manually transferring all records for them, and printing everything they need on paper.

When data is added, it's mostly new rows, such as a new item number with descriptor, vendor, etc., or a new sales order with pricing, or a new purchase order, etc.

I've built a few Access databases in the past, but always uncontrolled environment, one large file, etc. So I understand the basics like tables, queries, and forms, but I will have to learn about what access control features it may have for a multi-user environment.

Also, in the category of "time flies", I now realize that most of my work in Access was probably 1995 - 2000, a full 20 - 25 years ago! It has probably been at least 20 years since I built an all-new database, but I work in Excel literally all day everyday, and I do recall enough functional similarities that I don't expect an enormously steep re-learning curve.

 

[riptides]

Until now, it has been entirely me, which is why Excel was a great starting point for this system. But now work has grown to where I'm having to bring in contractors and outsource, and I'm getting tired of manually transferring all records for them, and printing everything they need on paper.

When data is added, it's mostly new rows, such as a new item number with descriptor, vendor, etc., or a new sales order with pricing, or a new purchase order, etc.

I've built a few Access databases in the past, but always uncontrolled environment, one large file, etc. So I understand the basics like tables, queries, and forms, but I will have to learn about what access control features it may have for a multi-user environment.

Also, in the category of "time flies", I now realize that most of my work in Access was probably 1995 - 2000, a full 20 - 25 years ago! It has probably been at least 20 years since I built an all-new database, but I work in Excel literally all day everyday, and I do recall enough functional similarities that I don't expect an enormously steep re-learning curve.

So you can store the data in one database, and have the users in another database just doing adds and updates from another. You could even see who the user is, and present them only with a form for reports. They in turn are separated from the data and delegated to just providing whomever a report.

Are each spreadsheet unique data wise, or do they share common data fields? Worst case is you have x number of tables based off each spreadsheet you have. Best case is you have a few tables with lots of data.

 

[WinterDeere]

Are each spreadsheet unique data wise, or do they share common data fields? Worst case is you have x number of tables based off each spreadsheet you have. Best case is you have a few tables with lots of data.

Wow... getting into the details. You asked for it!

I tried to avoid any duplicate data entry, when creating the spreadsheets. But because your "form" is your "table" is your "report" in Excel, I do have may cells that auto-populate by pulling data in from other sheets.

For example, when calculating a BOM cost, I will have the BOM worksheet load the BOM for a given assembly number, then automatically find the latest revision of each item in the assembly from another workbook, and then go find the latest pricing from yet a third workbook. The pricing thing is a handful, very big formula that takes into account challenges like thee latest pricing maybe not covering the quantity in question, or older pricing that is at the right quantity but on an outdated pricing trend curve. Messy stuff, that Excel can handle quite well, but only when Excel's latest rendition of formula auto-correct doesn't break all links into the corresponding workbook.

Anyway, I haven't given it much thought yet, but I anticipate the database will have multiple tables, such as:

1. Item master
2. BOM master
3. Sales orders
4. Accounts
5. Approved vendor data
6. Customer data
7. Employee / contractor data
8. Inventory (could be rolled up into item master?)
9. Timesheets (could be rolled up into employee / contractor data?)
10. Leads tracking
11. Rates (for calculating project costs, and based partly on employee data, but partly independent due to other rates factors)

One thing I would want to ensure is that any Forms I create can easily auto-populate various fields from various tables. For example, when creating a BOM, I want the user to be able to enter an item descriptor and automatically see a list of available part numbers, or to enter a part number and then automatically see the corresponding descriptor.

 

[kenmbz]

Data validation with Access will help with sharing this.
We find Excel , even with the built in validation is too easy to bypass/typo.
And people always leave filters on and make a mess.

I hate access too, but SQL seems too much for this and a lot more work.

 

[WinterDeere]

And people always leave filters on and make a mess.

Yeah, simple things like "Fill down" can create a huge mess and ruin months worth of data, if applied with a filter on!

The behavior of filtered used to be more predictable and obvious, but like everything in Excel, has become more unpredictable or hard to predict in recent years.