How to build an Excel solution for use on a local network
Microsoft Excel workbooks usually contain a whole range of spreadsheets, each containing many different pieces of data. Working with them is not only time-consuming but can result in errors. For instance, manually inserted formulas can crash; we can accidentally miss formulas and forget to correct them if done manually. Hence the result is wrong, usually without us even noticing. We need automation that provides a stable data structure that can be added, recalculated, and refreshed arbitrarily in all tables simultaneously, ensuring a correct result. If you work with a team, you can use your Excel tables on a network and allow your colleagues to open, edit and save the workbook from their computers. All you need to do is build an Excel network solution.
It's a two-step procedure:
- First, create an Excel solution based on a single input table with associated code lists, which allows the production of automated interactive reports. With such an application, we can significantly reduce the time spent on daily tasks such as project management, cost planning, purchasing review, and similar.
- Excel solution with all functionalities (Data validation, drop-down menu, slicers, conditional formatting, counters, Prefix, Macro) and formulas (VLOOKUP, Today (), IF...) is transferred from a local disk to a local network. Each user gets their file; the files are identical, only the settings are different (e.g., defining a specific department in the company) or differentiated by content or purpose. A common database built on the network allows us to create interactive reports and KPI dashboards to review the results of the whole company.
Network solution advantages:
- Same, automated files (no manual work and formula corrections) for all departments, with department-specific settings (e.g., cost centers, chart of accounts, etc.)
- Centralized code list (controlled data), so all files have the same data to be entered, one person maintains the code list.
- The files are separated on the network by folders, and Windows work rights can be defined for every user (read-write, read-only, no access to the file).
- A common database that provides a comprehensive view of the company's operation through interactive dashboards and reports.
- The common data table/database can be linked with data from other CORE, ERP systems (Navision, SAP, etc.) to produce additional consolidated reports or transfer data via different formats (xml, csv, etc.) to other programs.
Figure 1: Example of file organization on the network
Figure 2: Example of an automated input table
See Projects 3 › – beginner solutions and Costs 1 › – advanced solutions for more information on building a network solution.