Pulling Column Info Based on Column Containing Data in Separate Sheet
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.
Best Answer
-
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!
Answers
-
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!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!