Is it possible to dynamically reference Sheets from a cell value?

gbenso

Hey, hoping it might be possible to get a little help here as the search feature just gives me 1000 permutations of 'link to the other sheet.'

What I'd like to be able to do is link to a sheet named in a particular cell. So if Column 2 Row 1 says 'First sheet' and Column 2 Row 2 says 'Second sheet', I'd like the same formula in Column 3 Row 1 and Row 2 to use a value from First sheet and Second sheet respectively. Essentially what I would get from using the INDIRECT function in Excel.

Obviously pointing directly at these sheets is easy in the example above, but for the actual use case I have 60 identical sheets that I need to pull around 15 values from, and setting up the links just for one sheet has taken half an hour. The Report function does this perfectly via the Workspace Select function and a filter on the sheet name, but since you can't then use the values in that report in another table its usefulness dies. I've considered using a Report → Export → Import → Sheet setup, but given that you Data Shuttle won't let you export from a Report either, you're forced to do this via downloading an export which is a no-go for this.

It seems wild that no one else hits this use case - I've only been playing with our setup for 24 hours and already come completely unstuck without this. Hoping it's because there's an obvious solution and the search feature just isn't quite showing me it…

Julie Fortney

Could you provide a screenshot of your sheet (with sensitive data blocked)?

gbenso

https://community.smartsheet.com/discussion/comment/458192#Comment_458192

Hi Julie, unfortunately not - there isn't anything in there not sensitive. I've created a little sample setup on the following post in case it clarifies. It doesn't really matter the content though, the question applies to any sheet you could imagine. If Column 1 says 'Sheet 1', 'Sheet 2' etc, all I need is to be able to have the equation in Column 2 use those sheets as references automatically, instead of having to manually create the ranges and name them in each formula.

gbenso

https://community.smartsheet.com/discussion/comment/458192#Comment_458192

Just threw this together. The formula in Return Sheet Column 2 would ideally return '1234' when in the cell it's typed into, and the same formula in the cell below that would return '4321'.

Does that make it clearer?

Julie Fortney

Thank you for the clarification. I don't know of a way to do this using core Smartsheet functionality. I haven't tried out this method myself, but it appears you can use the API to create them automatically:

https://smartsheet.redoc.ly/tag/crossSheetReferences

gbenso

https://community.smartsheet.com/discussion/comment/458266#Comment_458266

Thanks Julie! Didn't know that was an option. Not sure I'll be permitted API access by our admins but it can't hurt to ask. Should be possible to resolve this if I can - shame it's not built in functionality though.