Pull data from one sheet to another. Nothing working.

Daniel_CC1 · 03/29/23

I am trying to create a column formula which automatically pulls data from a sheet called "Employee Details".

I will need to do this with data from several columns but I will focus on the "Employee Name" column here.

I have already created a cross sheet reference between both sheets in the Sheet Reference Manager.

In both the "Employee Details" sheet and the destination sheet there is a column called "Employee Name".

I want a scenario where when new employee data is inputted into the "Employee Details" sheet that the "Employee Name" generates into the second sheet automatically.

The formula I am using is below but I keep getting the #circular reference error.

=INDEX({Employee Details Range 1}, [Employee Name]@row)

Any solutions would be hugely appreciated.

Abdul Tahlil · 03/29/23

Hi @Daniel_CC1

Maybe I can help. One method you can use is Smartsheet's Premium Application Datamesh which would automatically update the Destination sheet, provided you have this add-on.

In the case you don't have the add-on, you can use the Index Match function to enable you to populate the data. Could you provide a screenshot of the columns in question for both sheets? From there I can provide a more detailed solution.

Best.

Abdul

Andrée Starå · 03/29/23

Hi @Daniel_CC1

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

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

Daniel_CC1 · 03/29/23

https://community.smartsheet.com/discussion/comment/369427#Comment_369427

Hi Abdul,

Thank you very much for your response. I have sensitive date in the sheets so cant provide a screenshot but I can confirm sheet names and column names.

Source Sheet Name: International Nurse AWS Tracker

Name of columns needed from source sheet:

Candidate Name
Interview Date
AWS Status

Destination Sheet Name: International Nurse Onboarding

Name of columns that need to be populated on destination sheet:

Candidate Name
Interview Date
AWS Status

I basically just need a formula that takes that data from the source sheet and populates it in its corresponding column on the destination sheet.

I really appreciate your help.

Abdul Tahlil · 03/29/23

Thanks for the additional information @Daniel_CC1.

I agree with @Andrée Starå. You will have to create a Autonumber column on the Destination and Source sheet so the formula can pull in the correct information.

The Autonumber column will be on the Source sheet, and the Destination sheet would have a similar column where you would manually enter in the ID you would like the information to copy from.

After adding the column, you can create INDEX/MATCH formulas for the columns you choose in the destination sheet. The formula would be something similar to below:

Formula: Index([Candidate Name]:[Candidate Name], Match(RowID@row, [RowID in Source Sheet], 0)

The formula above searches for the Row ID that is an exact match in the Source sheet, and pulls the name into the Destination sheet. You can do the same for the Interview Date and AWS Status.

Hope this helps!

Best.

Abdul

Daniel_CC1 · 03/31/23

https://community.smartsheet.com/discussion/comment/369478#Comment_369478

Thank you for your Abdul,

So there will always be a requirement to manually enter some data? (copy the Row ID from the source sheet into the destination sheet) There is no way to have data automatically populate just by having a column formula?

Abdul Tahlil · 04/03/23

Hi @Daniel_CC1,

Correct. There needs to be an identifier for the formula to match the rows content. You could set up a COPY ROW automation, however, this copy's all of the columns, which won't work if the two sheets have different column structures.

Best.

Abdul

Pull data from one sheet to another. Nothing working.

Answers

Help Article Resources

Categories