Oct 06 2021 12:48 PM
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
Oct 06 2021 02:01 PM
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
Oct 07 2021 06:08 AM
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.
Oct 07 2021 07:51 AM
@mathetes Does it only work for 1 filter at a time? When I put it for Midwest for example it shows spill under BC.
See attached
Oct 07 2021 11:47 AM
Oct 07 2021 01:31 PM
@mathetes I got all of the formulas in except the ones where there are multiple people. I did watch the video and this is the formula I got when watching it
=FILTER(Overall!A8:O20,Overall!F8:F20=Jesse (Alberta)+Overall!A8:O20,Overall!F8:F20=Dan (Alberta))
it brings up #VALUE!
Oct 07 2021 02:44 PM
@mobrien6 You wrote:
I got all of the formulas in except the ones where there are multiple people. I did watch the video and this is the formula I got when watching it
=FILTER(Overall!A8:O20,Overall!F8:F20=Jesse (Alberta)+Overall!A8:O20,Overall!F8:F20=Dan (Alberta))
it brings up #VALUE!
You had some bad syntax in there. The range to be filtered is only named once, at the start (I crossed it out above). And then you needed parens and quotation marks in the criteria:
=FILTER(Overall!A8:O20,(Overall!F8:F20="Jesse (Alberta)")+(Overall!F8:F20="Dan (Alberta)"))
But let me take this opportunity once again to advise that you make that column F contain ONLY the geographical region. Mixing in another data type --the names of people--just complicates things and, frankly, is a database no-no to begin with. Add a distinct column for the names of the people, but have column F--which you want to use as the main criterion for this FILTER function--contain nothing but the geographical area. It'll be a LOT cleaner.
And a lot more flexible. If, for example--I don't know that this is the case, but if it were--you have Mary Jones responsible for multiple regions, you could do another filter based solely on her name, and see the data lines that pertain to the person across multiple geographic regions.
Oct 12 2021 07:59 AM
@mathetes thanks for your help I have all of the formulas entered however the color coding is not transferring over with the filter function. From what I see I think it has to do with conditional formatting that I need to input to have the colors in column A move with the information.
Can you assist me with what needs to be done for this?
Oct 14 2021 04:43 AM - edited Oct 14 2021 04:43 AM
I don't know what the condition is. Kinda need to know that in order to apply it, and I don't see what the basis is for the colors. (I see the words in column A, but not what in the data relates to that.
Oct 14 2021 05:21 AM
Oct 14 2021 05:49 AM - edited Oct 14 2021 05:51 AM
I know that's what you want. For conditional formatting to work, there needs to be a condition, in the data, that drives it. Are you just assigning the colors manually in the master sheet? If you are, may I suggest that instead of doing that in the master sheet, that you add a column of data in the master sheet where you select from a list (drop down) one of the characteristics that you've encoded in column A. THAT could become the basis for conditional formatting.
Oct 16 2021 12:40 AM - edited Oct 16 2021 12:50 AM
As a variant try this formula:
=FILTER(Overall!$A$8:$O$20,ISNUMBER(FIND(RIGHT(CELL("filename",$A$2),LEN(CELL("filename",$A$2))-FIND("]",CELL("filename",$A$2))),Overall!$F$8:$F$20)))
the formula just looks for the sheet tab name in column F as a criteria so it won't matter if its Joe (BC), or Jane (TO) or John (AB). As you can see in the formula is just a nested formula to find whatever the sheet tab Name is in column F in the Overall sheet tab.
Oct 16 2021 12:58 AM
Just copy the existing conditional format rule and apply each copy to the sheets that you want the formatting to execute when the condition is met.
Oct 18 2021 05:24 AM
Oct 18 2021 05:29 AM
You're on the wrong track, trying to get the color to transfer over with the data.
Add the condition as a column of data in both cases (the master data and the filtered data), and then the conditional formatting to each sheet. Add a column that reflects the data that you're using manually to assign a color on the master sheet...then conditional formatting works easily, whenever and wherever you want it.