Cross Reference Report - Workaround Ideas

ker9

Hi,

I know that I cannot cross reference a report; I'm hoping someone might have an idea on a workaround.

I have a report that gathers data from all sheets in a specific folder. The quantity of sheets varies around +/- 200.

I want to get one summary field (to match on) and the sheet link from each sheet into another sheet. I do not want to retain data from sheets that are completed and deleted.

Any suggestions other than having the sheets copy a row to another sheet, which would need a method to remove data that has been completed/deleted?

TIA

Lucas Rayala

Hi @ker9 -- I got a little lost here:

"I want to get one summary field (to match on) and the sheet link from each sheet into another sheet. I do not want to retain data from sheets that are completed and deleted."

Regardless, this issue seems to be the number of sheets and the fact that the number of sheets seems to fluctuate dynamically. That kind of rules out a formula, unless you use the API to generate it, or are crazy persistent at updating/creating new formulas. DataMesh would be even more upkeep, unfortunately.

I kind of like the copy row idea -- if you had a two-step process, where the source page first copies a row over when the row is created in the source sheet, and then copies the row when the row or action is complete, you could match the two copied rows using a unique ID of some sort from the source page and use a formula to flag them for archive using a move automation.

If this doesn't seem feasible, maybe provide some more context. How are all these sheets generated? How dynamic is the number of sheets.

Good luck!

ker9

Hi @Lucas Rayala

Thank you for your response. The biggest problem is getting the sheet link - it is in the report but I can't get at it to use it. Copying or exporting the data loses the link.

My report has an ID field (which I can match on) and the Sheet link for each sheet in the folder. I really want that sheet link, but there doesn't not appear to be any way to get it. It cannot be added to a sheet so copy row doesn't bring that information with it.

Lucas Rayala

@ker9 ah, I understand a little better now. You don't happen to have any programmers who can use the Smartsheet API? That would simplify things, this is a pretty easy API call. And / or would it be too much of an ask for your sheet owners to put the sheet link in their actual sheet? Presumably when you say "Sheet Link" you're referring to the Sheet Name report column?

Let me think on this a bit.

Lucas Rayala

Yeah, there are only two options that I can think of.

1.) use the API to grab the links and ideally put them in a usable location within Smartsheet, i.e. a lookup sheet. This is really simple from a coding perspective, but you have to have things set up (you could even probably get this created as a simple desktop executable if you paid someone online).

2.) have the sheet owners copy/paste the link into their base sheet and then pull/consolidate those links where you need them.

Lucas Rayala

Given that you have a report set up, would it be possible to set up another report that just had the list of sheets? Would that be helfpul?

ker9

@Lucas Rayala

Thank you for your thoughtful responses. Yes, I am referring to the Sheet Name column in the report, which contains a link to the sheet.

The people who setup these sheets have a lot of items to complete and I really don't want to ask them to complete yet another task, but we may end up doing that.

I need a list of sheet links, not just the sheet names.

I will see if I can get someone to help with an API.

Lucas Rayala

@ker9 yeah, I did some experimenting and wasn't able to pull the URL out of the name using any off-the-shelf Smartsheet capabilities. it's either a manual cut/paste of the URL or use of the API, I think. good luck! sounds like you're thinking in the right direction.