Automatically fitering values through based on a criteria

[Naz555]

Hi folks,

My question today is how to automatically filter data from a database based on a criteria.

I have a dynamic database which populates automaically and needs to feed substituent mini databases based on region (refer example). However, i cannot fathom how to do this since there are multiple reference points of the unique item (region) and hence vlookup/hlookup wont work.

Does any of you experts guide me in achieving my objective?

Many thanks,

Best,
Nazif

[:) Sixthsense :)]

In D9 Cell

=VLOOKUP(D6,G6:L15,MATCH(D5,G5:L5,0),0)

[FDibbins]

Im not sure that you uploaded the correct file? It does not seem to match what you said you wanted

However, to get the answer you show in that WB, you could use this...
=INDEX($G$5:$L$15,MATCH($D$6,$G$5:$G$15,0),MATCH($D$5,$G$5:$L$5,0))

[Naz555]

Sorry about the attachment, sending through the correct file.

[FDibbins]

See if the attached will work for you. I used a Pivot Table, all you need to do us change the value in C1

[Naz555]

Hey mate, thanks for that.

But i want the filtering into different tabs to work automatically. The Regions are divvied into separate tabs and i want a function in those tabs that will intelligently pick up the relevant rows from the database.

Cheers

[:) Sixthsense :)]

Put this formula's in each sheets.

In A1 Cell
=CELL("filename",$A$1)

In B1 Cell
=REPLACE(A1,1,FIND("]",A1),"")

Enter the below formula In any of the cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX(Consol!D$7:D$39,SMALL(IF(Consol!$C$7:$C$39=$B$1,ROW(Consol!$C$7:$C$39)-MIN(ROW(Consol!$C$7:$C$39))+1),ROW(1:1))),"")

Drag it down and right...

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

[Naz555]

SO i need to replicate the array formula in each of the tabs, is that right? Thanks!

[FDibbins]

OK try this 1....

[:) Sixthsense :)]

Yes exactly and also the formula's provided for A1 & B1 cells too

[Naz555]

Fantastic mate! Work of a genius!! Thanks!!

[FDibbins]

@ Six, thanks for the feedback

@ Naz...Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Naz555]

Hey FDibbins,

Your solution works great as well! Appreciate much you lending your expertise!

Cheers,

[Naz555]

Hi Six,

Based on your input I have constructed a table which works for certain criteria but not for all which I find to be bizarre. Please find attached a sample of the file (i had to make it much smaller in order to bring it below 1MB) and the contentious tab being the one named "Eastern Victoria". The area highlighted in orange contain the array formulas and the database sits in the tab named "Business Units".

You will realise that when the tab named "Eastern Victoria" (which is the criteria essentially) is changed to "Northern Victoria" the relevant information is pulled through without an issue, but it doesnt work the same for any other criteria.

Seeking your expertiese to me help me out.

Many thanks,
Naz

[:) Sixthsense :)]

You current D34 Cell Formula of Eastern Victoria sheet

=IFERROR(INDEX('Business Units'!B$2:B$781,SMALL(IF('Business Units'!$A$2:$A$781=$C$31,ROW('Business Units'!$A$2:$A$781)-MIN(ROW('Business Units'!$A$2:$A$781))+1),ROW(31:31))),"")

Just change the ROW(31:31) to ROW(1:1) like the below

=IFERROR(INDEX('Business Units'!B$2:B$781,SMALL(IF('Business Units'!$A$2:$A$781=$C$31,ROW('Business Units'!$A$2:$A$781)-MIN(ROW('Business Units'!$A$2:$A$781))+1),ROW(1:1))),"")

Drag it down and right...

[Naz555]

You are such a life saver! Thanks much mate!