Pros and Cons of Cross Sheet References vs Index Match formula for transferring core data from sheet

RobH

Hi all,

I am looking for some guidance and discussion on what is the best strategy for sharing some similar information between different sheets.

I am creating a PMO Template set along the lines of the Template Set available in the template store but with some changes to reflect my situation.

I have a portfolio of sub 100 projects and will have numerous sheets doing different tasks in the sytem. I want the different sheets to reflect the same basic project information from sheet to sheet. For example,, I have a basic project metrics sheet with Sart/Finish dates, Category, Status, Project Lead etc and I will have a seperate sheet with financial and budget information.

I am currently using cross sheet references to copy the Project ID, Name, Location info from the Metrics Sheet to the Financial Sheet.

I have been looking around and have seen some suggestions to use Index/Match instead for this type of task.

Can anyone summarise the pros and cons of these two different approaches please?

I think this suite of sheets and reports will end up being quite complex and I want to ensure that it is robust and can withstand adding rows and deleting rows for example. I dont envisage having to re-order rows but it may end up being required?

Your thoughts?

MichaelTCA

Hello @RobH

Index/Match can be very helpful if you have 1 criteria. Index/Collect can handle multiple criteria.

If you have data changing (being added or deleted), then using a function would be the way to go. If nothing is changing and will never change, than a function would not be necessary.

Keep in mind a cell function and a column function both use different amounts of data. Index/Match and Index/Collect also use different amounts of data. Each cross-reference can be met with another cross-reference depending on how many sheets require the same data and everything can slow down.

My opinion, you can analyze data easier when functions are set up to handle the changes automatically. It also provides a higher probability of accuracy and a degree of error handling.

If you plan on having a complex process, then develop the functions. The more complex it is, the harder it will be to manage, and functions help simplify data management.

I have a template setup for Sales that uses lookup functions from a few master logs as well, so if you are pulling data from multiple locations, definitely use a function. I had to split up the data into separate sheets because of the load on the network caused by cross references. Luckily, Smartsheet increased the limit since then so you might not have to worry about that.

Hope this helps!

RobH

Thanks for the feedback Michael.

I was leaning in that direction when I asked the question and as such I have implemented an INDEX/MATCH approach to gathering data from one sheet and using it on another sheet.

I have had success and the dynamic alterations work if I make a change to a line.

I am now, however, thinking I may be reaching the limit of number of Cross Sheet references of 100.

I want my primary intake sheet (Portfolio Metrics if you like) to gather at least one or two single cell references from a similar sheet for each project in my portfolio. I have about 85 projects currently and the Intake Sheet will be the destination sheet from the project sheets as Source. This means if I want to bring in say 2 cell references from each project sheet I will have over 170 Cross Sheet References (not counting the ones I have already created).

Does that mean that I will have to rely on simple cell references rather than the Cross sheet references for some references and will it be an issue blending the two types of reference?

Razetto

@RobH I'd suggest looking into using DataMesh configurations. It's a powerful tool when dealing with large amount of data.

https://help.smartsheet.com/articles/2476796-get-started-smartsheet-data-mesh