Dependant Drop Down lists without a macros - how to reduce the file size

[ashbee]

Hello

I've created a dependant drop down list in Excel to aid user entry but the size of the file is worrying. Empty it is 300KB, when I add my data in (around 6,000 rows) it goes up to 600KB. I could get around 100 responses (via email) that I would then need to attach to the relevant record in my Access database so I'm trying to find out if I can do anything to reduce the file size.

I tried two different ways of creating the dependant drop down list - both macro free, one used helper columns the other didn't both gave me similar file sizes. The form also needs vlookup to enter related data into other fields on the form. I've attached my sample file. I'm wondering if anyone would be able to advise on how I might get the file size down.

Ideally the solution needs to be macro-free but if this is impossible I may have to create two versions. A light weight macro version if possible and then a macro-free larger file (sigh!).

https://gofile.io/?c=WisXeX

Any ideas would be appreciated.

 

[RasGhul]

I got my version down to 203kb the biggest component is the color fill on the first tab, I'm not sure you could reduce it much more and keep the same functionality with Formulas.

https://www.dropbox.com/s/awpjbjshb... Dependant Drop downs Macro Free_jp.xlsx?dl=0

I'll have to the leave a VBA version to the guru's

 

[ashbee]

Thanks RasGhul, that's a good point, I'll make some small changes to the formatting to see if that will also help. I'll take a look at the code you amended a little more carefully this afternoon, thanks again.