Any solutions or workarounds to dropdown/cell-linking issues?

Kieran

Hi everyone!

It's a bit of a wall of text ahead, so apologies in advance and many thanks to anyone who dares read it.

I’ve got a bit of a beast of a project I’m working through and I’m hoping y’all would be able to help me work out some of the pain points I’m running into. Some of them could probably be alleviated with add-ons, but I’m looking for creative solutions because it pains me to pay more money for simple features that I believe every spreadsheet software should account for in this day and age. If there’s nothing to be done, I suppose I’ll bend the knee, but I’m making it my last resort.

The overall goal is to automate most of my company’s information flow and standardize our process. To add some context, we act as a wholesaler, importing products from overseas and distributing them upon arrival.

We currently keep track of our data in one giant spreadsheet, known as our Master Tracker. This thing is huge, probably 80 columns and thousands of rows. This sheet has some problems:

-It’s overwhelming to work in, especially for people who aren’t particularly comfortable doing spreadsheet work

-There are a fair number of formula columns that do nothing but bloat the spreadsheet

-There’s information that is only relevant during a particular stage in the shipping process and then becomes irrelevant as we get closer to receiving the product.

-The sheer number of columns becomes an issue, as we are constrained by Smartsheet’s cell limit. 500,000 cells are plenty if you have 5 columns, but much more of an issue if you have 80.

-We periodically clear out and archive old data to avoid reaching the cell limit

My solution to all of this was to flow the information through our process in 4 stages. The 4 stages are:

Quotes -> Active Orders -> On Water -> Received

I have automations set up so our account managers can check a box when they’re ready and the row will pass through to the next stage. This helps to spread out the data load (avoiding cell limit issues) and allows me to create reports account managers can use to visually see what stage each of their orders is in (I've color coded each sheet, so when you stack the sheets in a report you can clearly delineate).

Upon reaching the Received stage, I have helper columns and some automations set up to archive each row after a certain amount of time and funnel it into an archive based on the year it was ordered. This effectively closes the loop and in theory prevents data from piling up endlessly in the spreadsheets we work in daily.

So, that’s the framework I’m working around. Moving on to the pain points.

1. Lack of dynamic range for dropdowns - In the Master Tracker, we make use of a few dropdown columns to ensure some level of data integrity. Some examples of this are:

-Customer name

-Vendor name

-Item category

The options for these dropdowns continue to grow as we gain new customers, make use of new vendors, and try our hand at producing new items. I have separate sheets set up to act as directories for each of these, but Smartsheet doesn’t have an equivalent to data validation so I’m left having to manually copy and paste in any updates to our dropdown columns. This isn’t painful in the short-term, but I anticipate it being an issue as my company grows and we create more and more spreadsheets that may need to reference these data points.

2. No ability to dynamically mirror sheets - As I mentioned above, there are lots of columns in the Master Tracker that simply work as formulas which help to inform one or two important data points. Instead of having 10 columns in our main spreadsheet doing nothing but calculations, I’d rather migrate them to a backend sheet. I’d then mirror the relevant columns, let the calculations run, and pass back the one or two pieces of final data I need.

My current understanding is there are two ways to achieve something close to mirroring: cell-linking or utilizing the index function in tandem with a helper column.

Both methods feel flawed in the framework I’ve created. If I have a backend sheet set up with either method, it immediately breaks once I begin to flow information from one sheet to the next.

For cell-linking, the cell-links disappear as data flows from sheet to sheet, so I’d be left having to manually update the range. For the index function, the number in my helper column gets moved as data flows, so I’d also have to manually update.

Additionally, neither method accounts for the dynamic nature of the sheets we work in, so if the row count increases past what I’ve linked/indexed, we begin to have gaps in our data. What I really need is an equivalent to the ImportRange function from Sheets, with a range that remains static and updates as these spreadsheets are altered.

Any ideas would be much appreciated, even if it involves changing the framework I’ve set up. I’m sure I could have some degree of tunnel vision and don’t want to get so set in my methodology that I miss a simpler solution.

If you need more information/screenshots to help you understand the issues I’m having that’s no problem.

Paul Newcome

Screenshots would definitely help with context.

Dynamic Dropdowns: Currently the only way to have dynamic dropdowns are through the API or with Data Shuttle. With the proper setup and some consistent naming conventions, the API can be built to expand quite a bit across multiple sheets. Data Shuttle is less complex to set up but does not scale as well.

Sheet Mirroring: Are you able to point out (alongside the screenshots) which columns you need to move to a back-end sheet, what they are doing (formulas to be specific), how you want them aggregated, and what the final goal for those data points are?

Kieran

@Paul Newcome

Hey Paul,

Thanks for the timely response.

As far as dynamic drop downs, I figured that was the case, but decided to hold out hope just in case. My brother is a software engineer and I've already discussed the possibility of using the API to achieve this goal, so I'm glad to hear you say it scales well.

For sheet mirroring I'm happy to provide screenshots and formula, but it might take me a bit to pull together everything. I'll try to get it prepped in the next couple hours, but worst case I'll get back to you tomorrow.

Danielle Wilson

@Kieran Yeah, I'd agree with Paul that your best options outside of the API for dynamic mirroring of sheets / creating dynamic dropdowns would be Data Shuttle & DataMesh