Need Advice on Configuring a Sheet

Brandon Sills

Hello!

I am looking for advice on the best option to set up a sheet.

My goal is to create a blank sheet, set up a a few columns, and then pull in the Community Name for the "Active" Communities into a column stemming from the source sheet. I also want it to remove the communities from the row data when it becomes inactive. (Active vs Inactive are distinguished in the source sheet and are updated as new files are imported via Data Shuttle)

I was almost able to accomplish this with creating an attachment from the source sheet and triggering the attachment to update a DataTable via Data Shuttle…then linking a data table to the blank sheet. The problem with this is that once a Community is no longer active, I cannot remove it from the DataTable.

I looked into DataMesh but do not think this is an option, as I do not have any row data in the blank sheet.

Any suggestions on the best practice for setting something like this up other than utilizing an INDEX function?

Thanks,

Brandon

swfascetti

Have you looked into using datashuttle to update drop down menus based on a query? You would need a separate sheet that lists the Communities and their status.

Andrée Starå

https://community.smartsheet.com/discussion/122835/need-advice-on-configuring-a-sheet

Hi,

I hope you're well and safe!

It sounds like you should be able to use Data Shuttle to update the Dropdown options using a Master Dropdown List Helper Sheet.

https://help.smartsheet.com/articles/2482755-dynamic-dropdowns-data-shuttle

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Brandon Sills

Yes, I have looked at this. And it does work for this. I was just trying to be able to set it up to be able to pull into new sheets without having to do repetitive work.

I also have another stumbling block that is similar. In this other use case, In the source sheet, I have one column with all available lot numbers. In the destination sheet, I have 100+ Communities and I have a column for each community. The end goal is to select the Region via smartsheet form, and with logic will then list out the communities within that region, and then from there list out the lot numbers that are in current inventory… and then I am going to set up metrics for that particular community/lot number to display on a dashboard…. I have tested out utilizing data shuttle, (creating an attachment from the source sheet and setting up the automation to run the data shuttle workflow when a file is dropped). My intial thought was to map the same source column to each Community column in the destination sheet and utilize an "expression" to decipher which lot numbers correspond to that particular column. However, I was unable to figure out how to do this portion and I couldn't find much literature on this piece. I was trying to avoid creating 100+ configurations in data shuttle (to run for each particular column). It would be really nice to be able to apply filters to a particular column in the workflows.

Any ideas on a formula to input for an expression?

Thanks.

Brandon