Forum Discussion
Formula
Hello,
Looking to create a formula so that the overall tab column F where it says the name of the individual will pull into the individual tabs of the regions below.
So when we fill out the overall tab it will filter by a key word like BC to the tab BC
15 Replies
That's a fairly cryptic description--clear in your mind, I'm sure, but cryptic to readers; is it possible to post a dummy copy (no actual confidential or proprietary data) of the workbook you have?
That aside, it sounds like something the FILTER function could do. It's a new function, requiring the newest version of Excel. Here's an introductory video prepared by Microsoft: https://www.youtube.com/watch?v=9I9DtFOVPIg
FILTER does the job, although I used the actual entry in your column F rather than expecting it to search for the text BC in that column.
=FILTER(Overall!A8:O20,Overall!F8:F20="Brian (BC)")
I referenced rows through row 20, just to illustrate the point that it would filter a larger database. I wasn't able to copy your single row (I tried) to demonstrate that.
I do see that you have multiple people in the case of Toronto, and perhaps in others. I'd recommend either making this into two columns, one with the name, the other with the region OR just using a slightly more complicated FILTER criterion. The video I referenced above shows how to have multiple criteria and reference them with, in effect, AND or OR. In this case, to get each of the TORONTO folks into one sheet, you'd use the method that constitutes OR. It's tricky at the start, so use the video to educate yourself.
Here's the formula, which need only be entered in one cell. It spills to all columns and rows needed to satisfy the criteria. A more complete dummy workbook would have demonstrated that. I'd be happy to show how that would work, but you'll need to provide a more complete dummy database.
