Creating a custom filter based on a lookup table?

Hi, and welcome to the forum

You'd be better advised to change your format to a simple table that can be used by a standard Autofilter (or if more data columns should become involved perhaps a Pivot Table). i.e.

Please Login or Register  to view this content.

Originally Posted by goldenglow90

Attachment 304622

This is the main sheet I'm using. It will have data of pupils along the top and colour coded based on level of understanding. The codes for the objectives are down the side so I want to be able to set up the filter so I can only see objectives linked to a particular unit.

Richard, I don't think your idea would work as that should require duplicates of each objective which is repeated in another topic?

Hi,

Why do you think my table won't work? Your stated objective was

to be able to select a unit so it only shows the objectives in that unit.

My table does exactly that when you filter column A for the unit required. Your table contains duplicates so I'm not clear why you seem to be saying that's a problem. To do what you want to do it's essential.

Not sure what you mean by 'topic' since this wasn't mentioned in the OP.

Please upload a copy of your workbook - see guidance and reasons why in the rules area. Pictures are rarely much use and most of us are disinclined to recreate your workbook when it's readily available.

It would help if you could also create on a second sheet exactly what you want to see for a particular selection on the first sheet.

Hi,

Can you provide a table showing the relationships between objective references and unit numbers.

e.g what determines the 20 objectives you show for unit 2 from the 67 available. Make sure the table is complete for each unit number you have.

Maybe something like this...

Data Range

	A	B	C	D	E	F
1	Unit 1	Unit 2	Unit 3	-----	Unit 3	N3.4
2	N2.1	N2.1	N3.4			N4.5
3	N3.2	N3.4	N4.5
4

E1 could be a data validation drop down list.

This formula entered in F1:

=IFERROR(T(INDEX(A$2:C$3,ROWS(F$1:F1),MATCH(E$1,A$1:C$1,0))),"")

Copy down until you get blanks.

Originally Posted by Tony Valko

Maybe something like this...

Data Range

	A	B	C	D	E	F
1	Unit 1	Unit 2	Unit 3	-----	Unit 3	N3.4
2	N2.1	N2.1	N3.4			N4.5
3	N3.2	N3.4	N4.5
4

E1 could be a data validation drop down list.

This formula entered in F1:

=IFERROR(T(INDEX(A$2:C$3,ROWS(F$1:F1),MATCH(E$1,A$1:C$1,0))),"")

Copy down until you get blanks.

I'm not sure what you mean here. I cannot make any changes to the current format so any extra tables need to be added to a separate sheet which can later be hidden. Also, I don't need it to show me what objectives are in a unit, I need it to filter the current info to only display the objectives for that unit, if that makes sense.

Hi,

Does the attached help

@azumi

As I understand it the definitions of which observation types (N2.1, N2.2 etc,) belong to each unit are defined on the Criteria tab.

Originally Posted by Richard Buttrey

Hi,

Does the attached help

That's exactly what I want. The only issue is that this is a lot of work and coding when I have 4 year groups each with a workbook for number, algebra, data and shape so is there not a possibility of doing it without all of the VBA codes and defining data ranges? The curriculum changes slightly every couple of years so the objectives often change and move between units, it needs to be something easily edited. Thanks for all your help though.

Not entirely sure why you think there's a lot of work & coding. The coding after all is a mere handful of lines, and far from 'defining' data ranges as I think you mean, they are dynamic range names and the very antithesis of the defined ranges you have in mind since they adjust automatically. If you had bothered to test the scaleability by swapping observations between units you'd have no doubt seen how the system automatically adapts. Had I known you might want to add add additional units this too could have been automated so that all you would have needed to do would be to simply create a new list.

It's now clear that the question you originally asked was not the whole story.

It behoves all who ask questions here to tell us in advance the whole story otherwise we all waste our time putting together a solution for what we believe is a very specific arrangement rather than for a more generalised one. One which could have been put together had you bothered to mention it in the first place.

The very fact that you mention the variability, however small, AND that you want it to be something 'easily edited' necessarily means that the solution will need to be MORE automated and hence need additional code and functionality precisely because of your desire to keep the manual effort down.

I just wish you had mentioned the full scope in the first place. It would have been so much easier.