Pulling Column Info Based on Column Containing Data in Separate Sheet

Chris Malone

I am trying to figure out how to pull in the Name, County and Status from a master list onto a new sheet based on the master's Status column containing data. I am wanting to do this so that we are not overlooking anyone. I have been trying to figure it out and have seen that I probably need to use the INDEX and COLLECT functions but I am having a hard time figuring out how to make these formulas work with cross sheet references. Anyone able to help me figure this out?

Edit: these are sample sheets as my others have sensitive data on them. One other important piece of information is that some Statuses in my true master list are empty. I would like to have this formula pull only rows containing information as opposed to all of them. Did not think to show that difference when posting original screen shots.

Chris Malone

Created helper column containing Row # and used that as the row_index reference point. Final formula looks like this:

=INDEX(COLLECT({Name Reference}, {Status Reference}, <>""), [Row #]@row)

Hopefully this helps someone else!

Chris Malone

I have come to realize I could or should only do this one column at a time. I think I have figured out most of it but am running into an issue with not being able to drag the formula down or make it a column formula. Here is what I have:

=INDEX(COLLECT({Name Reference}, {Status Reference}, <>""), 1)

Any idea on how I can change the row_index to allow me to make it a column formula? The only thing I have figure out how to do thus far is to manually change it from 1 to however many rows I need. Any help is appreciated!

Chris Malone

Created helper column containing Row # and used that as the row_index reference point. Final formula looks like this:

=INDEX(COLLECT({Name Reference}, {Status Reference}, <>""), [Row #]@row)

Hopefully this helps someone else!