Easy Way to Link Multiple Cells to Different Sheets?

happinessisland

I currently use a 'summary sheet' which is used to track different dates from different smartsheets. Basically, we have it written horizontally so each sheet/job has it's own row within this sheet and the columns are used to track different milestone dates from these different sheets. Typically, we just have to link cells from those different smartsheets for each project to get the exact date cell we're looking for. I'm just wondering if there is an easier way to do this so I do not have to link 75 separate cells. Is there a formula or something that would work? Please help, thank you!

SSFeatures

Hi,

Just so I understand correctly, you have the summary sheet and multiple "source" sheets. Your summary sheet combines the dates from all of the "source" sheets?

Are all of your source sheets formatted identically to each other?

Does your summary sheet combine every single row in your source sheets, or do some rows get skipped? In other words, do you hand pick certain rows from the source sheets?

Best

PM_Reeves

Hi @happinessisland

I might have something similar set up. I have one summary sheet that has pertinent information from all projects.

All projects use the same template, and all feed into a PIMS which pulls info from specific cells. Again, all PIMS are identical. When linking cells from new projects to the summary sheet I simply select the whole row from the PIMS to add the links in.

It's not instant when a cell changes in the main project sheet, as it needs to go through two cell links, but 5 seconds maximum.

Cheers

happinessisland

Hi @PM_Reeves

That sounds like something that may be able to help. Is your PIMS within Smartsheet and is this something you created yourself? It sounds like it's almost acting a helper sheet. Our smartsheets all use a template, and the summary sheet is asking for specific dates, so we just go in and link the date cells for the same rows, different sheets, manually. How can I find more information on using a PIMS in Smartsheet?

happinessisland

Hello @SSFeatures

Yes, our summary sheet pulls in data from multiple source sheets, but they all use a standard template. not every row gets utilized, we just go in and we only want the date to pull from a row. For instance, in the summary sheet we have a column, let's call it 'Task 1', and each project/sheet will have a row. so, i will go down the column, go to the corresponding project sheet and look for the row in that sheet called 'Task 1" and then only pull the date cell from that row, which is labeled as a column named 'Due Date' in all of our sheets. I hope this answers your question. We hand pick the rows but they're the same rows in the same section throughout all of our smartsheets.

SSFeatures

Hi @happinessisland,

I have an idea for how to make this work. In your summary sheet, you can add a column named "Sheet Name", and a column named "Project Name". In these columns, you will manually enter the sheet name that you want and the project name that you want from that sheet. Then you'll use this formula to fetch the data from the correct sheet for that project.

This formula assumes that you have the following:

• Sheet Name column - a column containing the sheet to pull from.
• Project Name column - a column containing the project name within the sheet.
• Date column - the date to fetch from the Sheet.

=IF([Sheet Name]@row = "Sheet A", 
	INDEX({Sheet A Dates}, MATCH([Project Name]@row, {Sheet A Project Names}, 0)),
	IF([Sheet Name]@row = "Sheet B"),
		INDEX({Sheet B Dates}, MATCH([Project Name]@row, {Sheet B Project Names}, 0)),
		IF([Sheet Name]@row = "Sheet C"),
		...continue
)

This is how the formula works:

• If the value in the Sheet Name column is "Sheet A", then:
  • Look at Sheet A's project names using the cross reference {Sheet A Project Names}.
  • Find a row that matches the same project name in our Project Name column.
  • Grab the date from that row using {Sheet A Dates}
• If the value is "Sheet B" do the same thing as before, but for using Sheet B's cross references.
• Continue the same logic for all sheets.

You'll manually enter the correct sheet and project name, and then the formula will always automatically fetch you the correct date.

I hope this helps, let me know if you need any clarification!

PM_Reeves

Hello again @happinessisland,

What I have sounds similar to what @SSFeatures has my PIMS is a Summary Sheet. That's the end of the similarities though, mine is all done through cell links rather than formulas - the only formula I think I have is to grab the date of the last update.

Happy to show you how I use mine - I'm in Australia for time zone info.

Have a great day