Forum Discussion

formulasbrokemybrain's avatar
formulasbrokemybrain
Copper Contributor
Oct 20, 2023

How to auto-populate all values in a row based off a value in an array using it's corresponding row

I am looking to have rows of data appear in one sheet from a master list depending on specific criteria, I want the entire row to fill in the same way it is found in the master list on another sheet in a different row. I need this to work on excel online (through sharepoint). This is harder to explain than I hoped, hopefully someone can help me.

 

I have a project with participants who need to complete tasks and come from a variety of locations. I have a master list that includes all participant names and location and whether they have completed the task or not. I want to be able to have separate sheets for each location, where the participant information will all auto-populate as it is updated in the master list without having to put a formula in every single cell.

 

I want to be able to have a cell in a location sheet find the participant name in the master list, and fill the rest of the row with the same information as is in the masterlist, even though the rows will not necessarily be the same number between the two sheets. I used the FILTER function to add the names for each location into their corresponding location sheet, but can do that manually if it makes the rest not work.

 

A formula I got close with is 
=IF(VLOOKUP(C10,'Weeklies (ALL)'!C8:C35,1,FALSE)=C10,OFFSET('Weeklies (ALL)'!E10:CS10000,,,1,94),"")
however, I cannot figure out how to make sure that the name found fills in with the correct data.

 

I set up an example file to include here to play with. I have 5 sheets, a master list "Weeklies (ALL)", then one for each location "Location A", "Location B", "Location C", "Location D".

What I want to happen is if a participant name is found in the location A sheet, i want the entire row beside the name in that sheet to fill with the same information with the row of the same participant in the master list.

So if the name "Brantley Page" is in row 10 of the Location B sheet, but in row 13 in the Weeklies (ALL) sheet, how can I set a formula in cell D10 of Location B to find the correct row in the Weeklies (ALL) sheet for that participant and have all of the same data of that row, the appear in the Location B sheet? Is there a way to make it find the corresponding row to the cell value not a specific row? 

 

example tracking sheet 

 

Hopefully that makes sense. Any advice will be greatly appreciated. Thanks!

Resources