At the moment I keep the static key details in a VBA module. And then this is copied to each of the (currently) 50 spreadsheets that use the same system. It is the simplest way rather than updating each spreadsheet with a list of the variables (upwards of 100 for each year).
These details are held in arrays for each year. So there are a set of figures for 2017, 2018 etc. Each year (at least) 2 things happen. Firstly I add an extra year (or update the last year depending) and then likely add some new variables. Just now a custom function finds the relevant number based on a text code - so it grabs 10000 when I ask the function for the code "DA" for 2017.

The massive headache is actually updating all of these spraadsheets each time - or ratehr uploading them all again to our online server. If I could find a way to link from wherever the file is downloaded straight to external data that uses all of the current information held centrally that woudl be great. If it was all kept on our own internal servers it would clearly be easy without any need for the use of get external data. But the fact is that the variables would be better held centrally to query.

Does that shed any light at all on what I am working towards?