How to enlist specific information in a tab, referring to another tab?

Good evening everyone,

I'm new here, so my apologies if this question has been asked in the distant past before.

What I want to achieve in Excel at the moment, is the following:
I have a master database where I enlist the results of every Formula 1 driver in every single Formula 1 race in every single sub-category, in order of the data of the races being held.

Now, I would like to automatically create a second tab that shows only the results of one specific sub-category, namely the World Championship results. As far as I know, there is a formula for it to filter the undesired information out, but I can't find it nor can I remember. So I would love to get some help here to get me back on track.

See attachment for what I am actually talking about. Mind that I would like to create two different tabs, one for all results, one for only the World Championship results.

Thanks in advance for thinking along with me.

Since you are using Office 2007 you will need to array enter this formula in Blad2 C4. Then fill down and across.

Please see the attached as your regional settings may require ";" argument separators where mine use "," argument separators.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Good day, FlameRetired,

Thanks for the thinking. It is helpful, but for some reason not quite what I was looking for. Most because apparently my question was not clear enough, actually.

The conversion from ',' to ';' went okay while downloading and opening, so that was okay.

What I was looking for, is not only that the driverslisting (names and results) are searched after in a array-entered formula, but also that an array-entered formula filters out the other sub-series than 'WC'. And, another failure in my question, preferrably gets rid in tab 2 of the drivers who didn't race in the World Championship (like John Heath in this example, but that is my bad because I let him there in the example sheet).

Per haps it is just a matter of adding another array-entered formula in the series-line (in this case, the B-line)?

I am confused.

The formula (and my upload) return exactly what your sample indicates is expected.

What am I missing?

Perhaps another (more appropriate) sample upload could clarify the latest narrative.

And by-the-way welcome to the forum. I overlooked that this was your first posting.

Thank you, Dave, both for the welcome and for the answer you gave me.

Probably what is going wrong, is that the array-entered formula - copied into my original sheet - mostly refers to a cell that doesn't contain "WC". That is, because I just copied the cells from one tab to another (using =Tab1!E2 in this case), so the formula you gave me isn't wrong. It is just that the reference cell is not showing the right value ("WK").

Hence, if we go back to the original sheet, what is happening is that the nWC-nWC-WC-Tasman-nWC-WC are still in the second tab. Are you still with me? Hence, the cells in tab 2, referring to the sub-category, should also have some sort of array-entered formula (in Dutch we call it a 'matrix') to filter out only the desired references ("WC", or, in Dutch: "WK"). Still with me?

When line 2 is filtered out to only the desired contents, then the formula you gave me will work (because I checked it). So, it's not the formula itself that is wrong, it's my inition quesion that was not complete (my bad).

I hope you are willing to help me again. BTW I altered some things in the example sheet, to match it more like my original is. As stated in the newly uploaded sheet, column C is hidden, because it is only used for the VLOOKUP function, but it is a nessecary step to take in my experience.

So far, I have been playing around with a couple of 'old' array-entered formulae but I can't get it working. Usually I can, but I can't get the results I am looking for.

Again I would like to apologize for the badly formulated question at first - it's no excuse but it was close to midnight and I woke up that morning at 4am. I just wanted to ask the question and I didn't think it through well enough.

In the attached to get the Names output see the array entered formula in A11 filled down and across column B.

Formula:

Please Login or Register  to view this content.

For the counts(?) see the array entered formula in E11 filled down and across column F.

Formula:

Please Login or Register  to view this content.

In the original sheet, I have at the moment 305 drivers (D5:D310) and counting and the races span from S3:DC3 (with room to spare to CAD3 and counting).
For some reason, when I adjust the original array-entered formula with this cells, it doesn't work. Any ideas how to, please? I'm not a nitwit in Excel, but I feel like one at the moment... :-(

I would suggest dynamic named ranges (DNRs) to resolve those.

If you are not familiar with DNRs these are range formulas (in Name Manager) that resize dynamically to fit the data. There are 3 of them in the attached.

These are their formulas in Name Manager (and pasted in Blad1 columns L:M).

	L	M
8	Names	=Blad1!$A$3:$B$3:INDEX(Blad1!$A:$A,MATCH("zzzz",Blad1!$A:$A,1))
9	races	=INDEX(Blad1!$3:$3,MATCH("zzzzzz",Blad1!$2:$2,1)):INDEX(Blad1!$E:$E,MATCH("zzzzz",Blad1!$A:$A,1))
10	sub_Cat	=Blad1!$E$2:INDEX(Blad1!$2:$2,MATCH("zzzz",Blad1!$2:$2,1))

With DNRs it will be important to NOT put non relevant data to the right of or below the named ranges defined in this way. Otherwise those will be included in the named ranges.

Now the formulas array entered for names in Blad2 are

Formula:

Please Login or Register  to view this content.

and for counts

Formula:

Please Login or Register  to view this content.

Also consider dynamic ranges (not named) for the 'Finish' column formula

Formula:

Please Login or Register  to view this content.

Thank you. I will go try work this out in the original sheet... :-)

So, I am trying to work it out, and first thing I do is making sure that the refereces are correct, so that a=a in the example = original, and 1=1 in example = original (if you get what I mean...

Any way, as you might have seen, is that there is a column C in this sheet, and I have to use that one, because there are drivers with the same familynames (like the Rodriguez-brothers, father and son Villeneuve, the Schumacher-brothers, and so on). That would mean that the second in line (alphabetically) would get the same results as the first, if I refer to the familyname alone, correct? Hence the column C.

Now, the thing is, that the formula (array entered) doesn't work any more when I change column A into column C... Any idea why that is?
Yeah, I know... I'm difficult...

"I don't know" is an answer, too, btw... ;-)
Thanks again, for some reason I just can't get things fixed. I know it is possible, though. It should be...

If you are going to change the index of name for columns A:B to column C then it could be accomplished using the following modifications:
1. For the named range Names:

Formula:

Please Login or Register  to view this content.

2. For last names:

Formula:

Please Login or Register  to view this content.

3. For first names:

Formula:

Please Login or Register  to view this content.

4. For concatenated names*:

Formula:

Please Login or Register  to view this content.

5. For finish:

Formula:

Please Login or Register  to view this content.

6. For columns E and F*:

Formula:

Please Login or Register  to view this content.

*Denotes array entered formula (see post #2)
Let us know if you have any questions.