How do I write a formula to use multiple columns in a single cross-sheet reference.

Jonathan Gann

I am trying to set up a "combined budget" sheet that will summarize multiple budgets while maintaining the heierarchy of the original sheets.

Doing this on other solutions, I have previously just set up individual column references between each sheet (most projects utilize 2 separate budgets). This will not work on this solution because I am referencing 24 columns per sheet, and need to reference those columns on 9 different sheets. I cannot use a report for this, as it will not sort correctly, and the heierarchy of indented rows will not carry over to a report at all.

Another caveat to this is that these sheets are part of a Control Center template, so most external app usage is not able to be utilized (DataMesh, Bridge, etc.). It is imperative that I accomplish this through linking the sheets together.

To specify further and summarize: I need to write a formula that will use multi-column references from other sheets, as single-column cross-sheet references will be too numerous.

Jgorsich

Depending on what you are trying to pull off, this may be straightforward or may be an absolute nightmare. Certainly you can make a cross sheet reference to more than one column - figuring out how to use it in a formula is a bit trickier. With no direct equivalent to the "offset()" function in excel, the two methods I've seen used are vlookup() and index({inputRange},match(),match()) with the input range being multiple columns in a single reference. Both have weaknesses and ways they can go wrong, but are paths that might work for you. More specific advice would require more specific levels of details.

Jonathan Gann

As a follow-up, the exact use case I'm thinking of is not possible with Smartsheet's systems.

https://community.smartsheet.com/discussion/comment/475304#Comment_475304

You are correct that the equivalent to those functions aren't in Smartsheet, so generally it seems the only way to actually use multi-column setups is through SUM, AVERAGE, and similar formulas that summarize the entire two-dimensional range rather than try to extract from individual columns.

I was a bit overzealous in which columns I needed to reference, as many of them had formulas themselves and could be excluded from linking. I'd say as a follow-up here to just make sure to scrutinize what you're attempting to bring in and reduce/optimize as much as possible beforehand.

Jgorsich

Depending on what you are trying to pull off, this may be straightforward or may be an absolute nightmare. Certainly you can make a cross sheet reference to more than one column - figuring out how to use it in a formula is a bit trickier. With no direct equivalent to the "offset()" function in excel, the two methods I've seen used are vlookup() and index({inputRange},match(),match()) with the input range being multiple columns in a single reference. Both have weaknesses and ways they can go wrong, but are paths that might work for you. More specific advice would require more specific levels of details.

Jonathan Gann

As a follow-up, the exact use case I'm thinking of is not possible with Smartsheet's systems.

https://community.smartsheet.com/discussion/comment/475304#Comment_475304

You are correct that the equivalent to those functions aren't in Smartsheet, so generally it seems the only way to actually use multi-column setups is through SUM, AVERAGE, and similar formulas that summarize the entire two-dimensional range rather than try to extract from individual columns.

I was a bit overzealous in which columns I needed to reference, as many of them had formulas themselves and could be excluded from linking. I'd say as a follow-up here to just make sure to scrutinize what you're attempting to bring in and reduce/optimize as much as possible beforehand.