How do I auto-populate information from one tab to another in sheets?

Copper Contributor

I have an excel sheet with several tabs. The first tab is the Master tab used to track all tabs. I would like all new tabs to auto-populate information into columns on the master tab. For example, I have multiple fields in my form I would like information from these fields to auto-populate into the master spreadsheet. I would like all new forms/tabs to automatically update the master spreadsheet. I am currently entering everything manually.

24 Replies



Use VLOOKUP, as shown in the attached file (and the image below).



Here, too, is a helpful on-line resource that can explain in greater depth how to use VLOOKUP as well as some of the other variants that Excel offers to retrieve information from a separate table of data.


By the way, in the future it would make sense to start your inquiry here as a totally new thread. What you're asking isn't really the same as the prior exchanges on this one, and your inquiry could easily get buried. 

Do you know if there’s a way for excel to recognize a word and then put the thing next to it into a separate sheet?

Not sure if that makes sense for example:
on sheet 1 I have
“Katie” “Assignment 1 F+”
“Hayley” “Assignment 1 A+
“Katie” “Assignment 2 C-“
“hayley” “assignment 2 B+”

Is there a way to make it auto generate in sheet two:
Katie “assignment 1 F+”
Katie “assignment 2 C-“

And in sheet three
hayley “assignment 1A+”
“Assignment 2 B+”

I don’t necessarily need the names repeated but if the names trigger the next cell to be auto generated in a separate sheet? Does that make sense? Do you know?



You wrote: Do you know if there’s a way for excel to recognize a word and then put the thing next to it into a separate sheet?


And the answer, typical for Excel, is that there no doubt are multiple ways to do it. 


But also, typical for Excel, it makes a LOT of difference how you're entering that data in the first place, what the whole process is, what the desired outcome is.


If I were creating something along the lines you hint at--which I assume is a way to track grades for a class in school and have Katie's and Hayley's grades all summarized neatly, maybe averaged, etc.


One thing to keep in mind is that Excel is really good at taking what I'll call "clean data" in a consolidated database, and then extracting meaningful subsets of it.  I've attached a sample of what's possible. Note that on the DB sheet, each of those data elements is it's own column--that's what makes it "clean". 


Then on the Individual Record sheet, you can select a name and see a list of the assignments for which there's a grade. There's really no need to have a separate sheet for each person, just use this 'dashboard' screen to produce a summary of grades.  By the way, this solution does require the most current version of Excel in order for the functions FILTER and UNIQUE to work.



I'm look at doing the opposite of what the spreadsheet is showing here. I would like to be able to take the data in the table and put it in a form. How would I do that?




So I'd seriously suggest starting a new thread, with a bit more information. And, if possible, a copy of whatever you're working with.