Can an automation reference a column from a cell value?

_Ernest_

I'm trying to track which users are granted access to various systems. What I would like to do is to have one grid which lists all the System IDs (and various properties as columns) with one system per row, and a unique System ID for each system.

Then I would like a grid that lists each user as a row, and the column headers are the unique System IDs. When a user is given access the cell for that system indicates the access type. (User, admin, etc.) This is simple if manual, but manual means errors will occur.

So I want to take a user access form, which, after approvals, will update the appropriate instrument column for the user. Since the user access form would have the System ID in it (dropdown, kept updated by data shuttle), it has the correct value for the column. Here's where I get stuck though - how can I convert this value to a column reference and automatically populate the target cell with, say, "User"? It seemed like it would be an easy thing to do, but…

ChristianFinke

Smartsheet doesn’t let you directly use a cell value to reference a column. One thing you can do is set up helper columns for each system in your user grid. Then, use automation rules to update the correct helper column based on the System ID selected in the form.

This setup isn’t fully dynamic, but it lets you automate updates without manual errors. If you need true flexibility, tools like Zapier or Power Automate can add more dynamic referencing.

Let me know if you need help with any part of this!

ChristianFinke

Smartsheet doesn’t let you directly use a cell value to reference a column. One thing you can do is set up helper columns for each system in your user grid. Then, use automation rules to update the correct helper column based on the System ID selected in the form.

This setup isn’t fully dynamic, but it lets you automate updates without manual errors. If you need true flexibility, tools like Zapier or Power Automate can add more dynamic referencing.

Let me know if you need help with any part of this!

_Ernest_

Thanks Christian. That's unfortunate news. I'm not sure the helper column is the best approach - seems hard to maintain as we go and add more systems.

Can a column be referenced by its position (e.g. "3" for the third column) perchance? If so, I could list the column header values in row 1, and index the column that way.

Worst case, I can probably just query updates by user and filter for the most recent entry.

ChristianFinke

@_Ernest_

Of course Ernest. It is unfortunate. Smartsheet doesn’t allow referencing columns by position (like “3” for the third column) in formulas or automations. It always requires specific column names.

Again, for dynamic updates, tools like Zapier could help by processing data outside Smartsheet and pushing it back in. If filtering by the most recent entry per user works, that might be simpler to manage as you add more systems.

Let me know if you need help with any of this!

_Ernest_

Thanks again Christian. I'll have to browse Zapier, but if I can keep it simple in Smartsheet, that's a win for all the admins. More COLLECT statements for me!