How to pull data from one sheet to another easily

Xillin

Hi everyone, I manage smartsheet for my company even though I have nothing even resembling training in spreadsheet manipulation, formulas, etc. So I'm just learning on the fly. We have one sheet that has all of the projects we are currently working on, listed by project number and then a bunch of data specific to the project, each project only taking up one row. We have a few other sheets that reference those same projects from the first sheet, but for different purposes. What I'd really love to do, is set it up so that when I punch in the project number in one of those secondary sheets, it scans the first sheet for that number and auto inputs several relevant data points so we don't have to manually do it for every project. Is this possible? If so, can someone explain to me how?

Antonio Figueroa

I believe so. Try this

Create a new sheet. write a job# in the first column first cell and in the second column first cell use the below formula

=vlookup(Search_Value,Lookup_Table, column_Num, Match Type)

First type in the =vlookup(

Next we have to input the Search_Value. The search value will be the job# on the sheet you are on now. click in the first cell in the first column then type a comma.

Next we have to get the lookup_table

for this you will have to reference another sheet.

After you click the reference another sheet it will open a folder. Search for the Sheet you wish to pull the information from. Next you will have to click on the column with the job number in it and hold down the shift Key. While holding down the shift Key click on the column that has the data you wish to retrieve.

Now you can let the shift key go. But before you click insert you have to count the number of total columns that start at the Job# to the data. You will need this in the next step. Click insert.

Once you Click insert it will take you back to the formula. it will look something like this but with your sheet name in it.

=VLOOKUP([Job#]@row, {Project Plan Range 1})

Next you are going to put a comma after the } but before the ) so it will look like this

=VLOOKUP([Job#]@row, {Project Plan Range 1},)

Next put in the number of column's that you counted and then another comma and the type in the word false. the word false finds the exact match. So your final formula will look similar to this

=VLOOKUP([Job#]@row, {Project Plan Range 1},2,False)

except you number might not be a 2.

After you do this you can repeat for as much data as you want to retrieve. But remember to always count the Column's because it will be different each time.

Steve Patton

We run our projects in a similar fashion, with a "Master" sheet (one line item) and then several projects feeding from and back to this master sheet. This doesn't answer your question as it is ask, but it is relational on how we have done ours and may help -

1. Project is submitted through an automated collection form that feeds directly into the Master Sheet (so can be entered manually by the Admin or submitted through the form by anyone)
2. We have a pre-built template that is manually generated as "Project X"
3. We then use the "copy to another sheet" function and copy the line item to the new project (project x)
4. Links are then built in as necessary

The whole process takes about 5 minutes per project or less to set up. To your question, I think this is looking at it from a different angle, but may still accommodate what you need to accomplish. Just a thought, hope something is found helpful.

Antonio Figueroa

Good Morning,

Are you familiar with V-Lookups

Xillin

https://community.smartsheet.com/discussion/comment/307955#Comment_307955

I am not. I'm assuming that's what I need?

Antonio Figueroa

I believe so. Try this

Create a new sheet. write a job# in the first column first cell and in the second column first cell use the below formula

=vlookup(Search_Value,Lookup_Table, column_Num, Match Type)

First type in the =vlookup(

Next we have to input the Search_Value. The search value will be the job# on the sheet you are on now. click in the first cell in the first column then type a comma.

Next we have to get the lookup_table

for this you will have to reference another sheet.

After you click the reference another sheet it will open a folder. Search for the Sheet you wish to pull the information from. Next you will have to click on the column with the job number in it and hold down the shift Key. While holding down the shift Key click on the column that has the data you wish to retrieve.

Now you can let the shift key go. But before you click insert you have to count the number of total columns that start at the Job# to the data. You will need this in the next step. Click insert.

Once you Click insert it will take you back to the formula. it will look something like this but with your sheet name in it.

=VLOOKUP([Job#]@row, {Project Plan Range 1})

Next you are going to put a comma after the } but before the ) so it will look like this

=VLOOKUP([Job#]@row, {Project Plan Range 1},)

Next put in the number of column's that you counted and then another comma and the type in the word false. the word false finds the exact match. So your final formula will look similar to this

=VLOOKUP([Job#]@row, {Project Plan Range 1},2,False)

except you number might not be a 2.

After you do this you can repeat for as much data as you want to retrieve. But remember to always count the Column's because it will be different each time.

LookW

VLOOKUP is only good for the first data point or the one at the top. What if we wanted to pull in all the data with that job name/number?

Genevieve P.

Hey @LookW

Try a JOIN(COLLECT combination in that case 🙂

Here's more information: Gather all matching content into one cell