Google Spreadsheets: conditional, includes rows imported from different spreadsheets

Question

I have imported columns from several spreadsheets and different tabs (using an importrange within query) to one column in my master sheet. Now I need a formula that will fill in a different column with specific strings depending on where the cell in the first column came from. More specifically, I have a spreadsheet file documenting information about a school's students (their names, ages, ID numbers, etc.), each section has a seperate sheet. On the other hand, I am working on a master sheet, where I imported the names of the students from all sections into one column -the goal is to summarize the information in one place-, so now I want it get the corresponding name, find it's source (the specific sheet from another document), and just copying the name (since the name of the sheet=the section).

How difficult is it to do that?

It would be great to get some help!

Thanks in advance :)

Answer 1

From what I understand you're trying to achieve, it seems to me like you're going in reverse.

The importRange function already requires the information you want to get in your second column (where the range comes from) doesn't it ?

If you need the actual name of the spreadsheet then some Google Apps Script may come in handy, notably Spreadsheet.getName() that you can call inside a custom function.

You could very well have in your master spreadsheet a dedicated sheet that would indicate to your several importRange functions which range (sheet and reference) from which spreadsheet (let's say urls) to import. You would then concatenate these two to pass it to the importRange functions.

Hope this helps you to move forward, don't hesitate to clarify if I misread you.

Cheers