Formula

Copper Contributor

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

@mobrien6 

 

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

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...

@mathetes  here is a dummy of the workbook. 

@mobrien6 

 

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.

@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

You had entered something in column O and that interfered with the function's working. Once I deleted it, it worked. Whenever you see the SPILL# error, it's because something is blocking the full results. Have you looked at the YouTube video? I really do recommend it for a full explanation.

@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!

@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.

@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? 

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.

The colors are based on when someone is ready to be hired. And the name is colored to reflect the manual at the top for tracking purposes. I want the color to transfer with the filter formula over to the other sheets.

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.

@mobrien6 

 

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.

Yea_So_0-1634369925746.png

 

 

 

 

 

@mobrien6 

 

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.

=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)))

this formula brings up the same information with no color to go with it. What existing conditional format rule do I copy to each sheet.
I know where to go for conditional formatting and that I need to put it in each sheet but I'm unsure of what I need to put in the conditional formatting to get the color to transfer with the information.

@mobrien6 

 

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.