Dynamic Cross Sheet References

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