Dynamic Cross Sheet References

Rachelle McKeown

Hello,

We are enjoying using Smartsheet for HR tasks. I am wondering if we are missing some basic cross sheet referencing. I am trying to implement a Master Contact List. The goal is to have ONE list of all employees, where this list will dynamically feed all sheets that reference employees with automatic updates when employees are hired or terminated. i.e. if Bob Bobson is hired, we add him once into the Master List and all other Smartsheets will automatically know all of his information.

For example, we have a survey form that each employee is required to complete daily. We have a second sheet that references the Master List via linked cells to make a list of all current employees, and keep track of who has not submitted their survey each day. However, when we hire or terminate an employee, we need to go into this form submission sheet and manually add or delete employees to the list, which then uses a Vlookup to reference the Master Sheet. Is there a way to have this survey submission form automatically be fed from a master sheet?

In Excel, you can simply select a range from another spreadsheet to directly copy values over. In Smartsheet, it seems the only two cross reference formulas are Linking and Vlookup, which don't work here.

I have tried linking a whole column selection that includes empty rows beneath existing employees, but the linked cells in the form submission sheet don't refresh when new entries are added in the Master list. Linking connects two specific cells, and is not dynamic - i.e. new cells need to be manually linked with each new entry.

I haven't tried vlookups, because I'm not sure if I can create a universal formula that will grab information without a reference value to begin with.

Any ideas would be great!

Thanks,

Rachelle

Paul Newcome

I also suggest adding this to the master sheet, but to keep it separate...

You would insert a column to replicate the row number on both sheets. You would then "pre-fill" the 3rd sheet with the formula on as many rows as you anticipate needing at any one given time. So if you anticipate using no more than 50 rows at a time, I would suggest that you "pre-fill" 60 rows with INDEX formulas.

=IFERROR(INDEX({Master Sheet Column To Pull}, [Row Number Column]@row), "")

This will also keep the 3rd sheet in the same sort order as the Master sheet.

Andrée Starå

Hi @Rachelle McKeown

A solution that could be perfect for this use-case is the premium add-on, Data Uploader.

Is that an option?

More info: 

https://www.smartsheet.com/marketplace/premium-apps/data-uploader

Would that work/help?

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You should be able to use VLOOKUP or an INDEX/MATCH combination once a name is entered via the form to pull the information from the Master sheet.

I'm not sure I understand which list you are referring to when you say...

"For example, we have a survey form that each employee is required to complete daily. We have a second sheet that references the Master List via linked cells to make a list of all current employees, and keep track of who has not submitted their survey each day. However, when we hire or terminate an employee, we need to go into this form submission sheet and manually add or delete employees to the list, which then uses a Vlookup to reference the Master Sheet."

Rachelle McKeown

Hi there Paul.

https://community.smartsheet.com/discussion/comment/274076#Comment_274076

My apologies on the confusion here. In this situation, I'm referring to three Smartsheets. One is the Master List with all of our employee information. The second is a Survey Intake sheet that is populated when a survey form is filled out by employees daily. The third is a Submission List that checks for submissions of this survey each day; this sheet has a list of employees (fed from the Master list), with formulas to check the Survey Intake sheet if each employee has submitted a survey today. I'm referring to the third sheet, the Submission List, in the line you quoted. I currently manually add employee names or redo cell linking each time we hire a new employee to update the Submission List. My goal is to have this Submission List automatically update when new employees are added to the Master List.

Leibel S

@Rachelle McKeown

Is there any specific reason why you need the third sheet?

You can just add a column in your master sheet that tracks if the submission was put in today.

You can put in criteria into your formula to ignore the employee when they are terminated.

Then make a report specific for these columns that you review every day.

Rachelle McKeown

@Leibel S Thank you for your question. The Master Sheet information is to be used in several different sheets with several different requirements for formulas and automation. It seemed more 'clean', if you will, to create a new sheet that can then house formulas and automation specific to the daily survey only. I fear having too many different processes active in one sheet may confuse editors and viewers. Does that make sense?

If there is no solution to this issue however, we may need to take your advice and perform these functions on the Master List. But I do feel like there is a way to duplicate and automatically update this Master Sheet information on another sheet!

Paul Newcome

I also suggest adding this to the master sheet, but to keep it separate...

You would insert a column to replicate the row number on both sheets. You would then "pre-fill" the 3rd sheet with the formula on as many rows as you anticipate needing at any one given time. So if you anticipate using no more than 50 rows at a time, I would suggest that you "pre-fill" 60 rows with INDEX formulas.

=IFERROR(INDEX({Master Sheet Column To Pull}, [Row Number Column]@row), "")

This will also keep the 3rd sheet in the same sort order as the Master sheet.