CODEWITHSUNDEEP
google-sheetsimportrange
Jamie Johnson
Jamie Johnson

Reputation: 1

Import data between Google Sheets depending on whether specific data matches

I am including a quick screenshot that shows my question in a slightly more logical way, but basically I have two separate files, and would like to pull data from one into the other from specific locations, IF the names are matching from one cell to the other. Hopefully that makes sense:

Importing specific data between sheets if specific cell text matches

Update Here is the sheet where the data is being pulled FROM: https://docs.google.com/spreadsheets/d/1sfrmjqULdw4ELRIBmDURzlnIN0EzvpMCwHAmj8SuTMU/edit#gid=0

Here is the sheet where I would like to pull the data INTO: https://docs.google.com/spreadsheets/d/1HvPGl0ZjAO29DPTDkmpMVu9GFQIUJHqxOZF_itwt1to/edit#gid=1458193311

I should also note, that while I somewhat understand that I can ImportRange and specify some details, how do I specify a range related to a specific name, if that range is different for each name? I.E. Student 1 has data from A2:D10, but Student 2's data is F2:J10...etc.

Can we specify column and rows according to the position of a name?

Upvotes: 0

Views: 2257

Answers (2)

Jamie Johnson
Jamie Johnson

Reputation: 1

Thanks @Ruben! I ended up using the following formula with a Named Range (StudentLookup), and then edited which column it pulls from, for each corresponding cell and it's data:

Cell B6: =INDEX(StudentLookup,MATCH($A6,other!$A$1:$A$9,0),MATCH($B$3,other!$A$1:$Z$1,0))

^for data I wanted in that cell and then for the next cell and so on:

Cell C6: =INDEX(StudentLookup,MATCH($A6,other!$A$1:$A$9,0),MATCH($B$3,other!$A$1:$Z$1,0)+1)

And I also just pulled the data directly from the other workbook into another sheet in this workbook for easier reference. Thanks again!

Upvotes: 0

Wicket
Wicket

Reputation: 38356

From the question

how do I specify a range related to a specific name, if that range is different for each name? I.E. Student 1 has data from A2:D10, but Student 2's data is F2:J10...etc.

If the reange doesn't include a key, then you should make a relationship table setting for each name the corresponding range reference as text, then use VLOOKUP, QUERY, FILTER, INDEX/MATCH to get the corresponding range reference.

Let say that we add a sheet as set it's name as Table. Then con Column A there are the names and on Column B the corresponding ranges reference. If the name to be imported is on A1, the formula to use could be the following:

=IMPORTRANGE(spreadsheet_key_or_url,VLOOKUP(A1, Table!A:B,2,0))

Upvotes: 0

Related Questions