Redesign of Current Excel System

[JackDanIce]

Hi,

A client performs a daily process to record data, using Excel as follows:

Input
Single table with fields:

Date (type date and time)
Name (type string)
Size (type positive mixed number, 2 d.p)
Gravity (type boolean)
x1, x2, x3 (type positive mixed number, 4 d.p)
Total (type mixed number, 2 d.p)
Pandora (type boolean)
Image (PNG file, url to image saved in local folder with a defined file name pattern)

Save as a named table Data on sheet Data. Currently, two Excel spreadsheets are used to track this data.

Spreadsheet Input
Data - as above, I use VBA to call .ShowDataForm for ease of data input into this table.
Summary - Cover sheet with subtotals from the Data table
Day1, Day2, ..., Day5 - five sheets for each workday of the week with same data as rows from input table and an inserted picture and a comments field (text, no size restriction)

Each DayX sheet is named <DD MMM YYYY>

Spreadsheet Master
Every five days/weekend, Day1, Day2, ..., Day5 are moved from Spreadsheet Input into Spreadsheet Master which has it's own Summary sheet (identical to Summary in Spreadsheet Input).
Data table in this file only has new records added to it from Spreadsheet Input.

Spreadsheet Input is then restarted by clearing all contents of DayX sheets and update sheet names to reflect the next week.

Reason for two spreadsheets:
- Working file which stays small
- Master file for backup and longer term data analysis.

Eventually Summary sheet in both files will be replaced with a dashboard/pivot charts and use of PowerQuery with pivot chart output.

This process started in September and unsurprisingly Spreadsheet Master is large in size relative to the Input file; there is only 1 user of this process.

I'm looking for suggestions to reduce this to a single file but unsure how to get around the image size issue without reverting to a database solution.
With macro's I've automated as much of above as possible.

The main problem is size as it's always going to grow and needing picture contents. The data table, resulting queries and dashboard output are going to be virtually fixed once set up.

This will also make reviewing aged DayX sheets difficult if just using left and right to navigate through worksheets (e.g. review a date a year ago)


Unsure if this is an Excel or Access or Other question, so posted to this thread, any suggestions to create a suitable tool for this?

Client isn't keen on a database solution because there is only a single table of data and some relatively simple queries for output.

Excel seems more suitable, but I don't want to recommend using Excel as a datastore because of the size and navigation issue, as well as need to maintain two files and worries about synchronising data in both Data tables.

Does anyone have any suggestions for a solution or work-around? I guess it's analogous to a daily blog or diary but with a summary dashboard.

TIA,
Jack

 

[offthelip]

My suggestion is that every 5 days instead of copying the data to the Master spreadsheet, you save a copy of the Input spreadsheet to a specific location and then create links in the master spreadsheet to each of the values from the "archived" worksheet that you want to display in the Master Spreadsheet. Also create a hyperlink to the archived input spreadsheet so that you can open it and look at the picture if you need to. This way you will create a master workbook in which you keep adding links to a new file, which means it will remain very small. EXCEL easily copes with links to hundreds of files so the is plenty of room for growth. Note I have designed a system that used this technique and it worked very well, it automatically created a new folder to save the files in each month, ending up running for a few years and with over a thousand files.

 

[JackDanIce]

Thank you for the suggestion offthelip

That sounds do-able, however, the client also needs to share the latest 5 days/master spreadsheet with one other person.

This may mean a cloud based approach but I think I can see how to use OneDrive to save the the Master Spreadsheet and linked 5day spreadsheet files.

Let me look into this further and will update the thread, but thread, thank you for the inspiration!

Separately, I tried to use RIOT, an image optimising app to reduce image file size but it had no effect. Each image is max 100kb size.

 

[offthelip]

Who says you can't build a relational database with EXCEL!!!

 

[JackDanIce]

Small update:

Only a single data table is required now (woohoo!) which doesn't need to be shared which just leaves a single file to insert 5 days worth of images and comments into that does need to be shared.

Using KISS and breaking these two apart, it's not an issue to create a weekly file to be shared.

Means images can still be kept on a local folder and weekly Excel files are saved just for the images and comments + returned feedback

So in the single data table, I can add a hyperlink or equivalent to the Excel file with it's related images and comments as the dashboard and data analysis of the table isn't dependent on needing to recall the images.

+ve: Less work to do, just a dashboard to design
-ve: Using Excel files as a data store.

Anyway, issue solved for now, thanks for reading and comments @offthelip!

 

[offthelip]

I am pleased to have helped!!