Pulling data from different sheets to master sheet based on single identical column

Mounika

HI @Andrée Starå

I have a master project sheet, where individuals from 5 subspecialities complete the form (from master project sheet) whenever they have the new project. This sheet collects the basic information like project name, objectives, subspeciality, summary etc. Whenever the individual submits this form, they (Respective person who submits this form) will get an email alert with Project ID (which is autogenerated in "master project sheet" and they navigate to their subspecialty area within the Smartsheet to complete another form (which is specific for each subspeciality) and complete the form within that sheet on various aspects of the project like Institution, city, country (where project is taking place), project status, start date, end date . In my case, I have five subspecialities.

Within each subspeciality sheet I have used formulas to pull the data from master project sheet, whenever a row is added in the subspecialty sheet, based on '"Project ID". Therefore, the information from master project form and subspeciality form is under same roof (under subspeciality sheet). And this makes data to be pulled from master project sheets to all subspeciality sheets.

Know, I also need the information submitted in "subspeciality sheets" (which are five in my case) to be pulled into "master project sheet" (Project ID column can be connected). Right now, I am manually linking the cells every time whenever a new project is submitted, and wandering if there is any process to automate this step.

Simply, I have five different subspeciality sheet, the data from all these sheets should be pulled into one "master project sheet" (which already has Project ID and some basic information about the project). Can you please help me find a solution for this?

Paul Newcome

Have you looked into an INDEX/MATCH or an INDEX/COLLECT?

Mounika

Hi @Paul Newcome

Thanks for your response! But I am looking to pull data from different sheets (where all have same column names, which are different subspecialty sheets in my case) to master sheet, based on unique Project ID. Is it possible to apply index/match formula in this case in my master sheet, where it will need to pull the data from different sheets?

Paul Newcome

You could, but after reading through your post again...

I would suggest using a move row automation to push each row from the master sheet to the sub-sheets based on your logic instead of fusing formulas. You can collect whatever information is needed there and then use a report referencing all 5 sub-sheets to recreate the "Master".