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.