Filtering Based on Fixed Value

Hi,

I am trying to create an overview worksheet in a spreadsheet that can dynamically list the a filtered list of results from another worksheet based on a fixed value.

If you look at the attached spreadsheet you will see that I have two worksheets:

1. Client Overview
2. Clients

What I am trying to do is make the "Client Overview" sheet populate with information automatically from "Clients". In the example attached the overview is for Coke, so I want to show all the Coke projects from "Clients" dynamically with their corresponding information. At the moment I have hard written the project information in "Client Overview", but I want to add multiple tabs for each client, and want this information to be pulled in automatically using a formula.

Is this possible?

Many thanks,
Jennsy

You don't really need to have one sheet per client - you can just have one sheet on which you can select the client from a drop-down and then have the other information automatically adjust. The attached file shows how you can achieve this with 3 basic formulae.

First of all, I have set up a Data Validation drop-down in cell B1 of the Client Overview sheet (the yellow cell), so that you can choose the client from the drop-down list.

I have used column H of the Clients sheet as a helper column, with this formula in H2:

Formula:

=IF(A2='Client Overview'!$B$1,MAX(H$1:H1)+1,"-")

Essentially, this identifies those records which match the choice made in the Client Overview sheet, and allocates a unique sequential number to each matching record. Hyphens indicate how far the formula has been copied down to (row 20), and in your real file you should copy this down beyond your data to ensure that you can cope with future additions.

I've also used column H in the Client Overview sheet, with this formula in H4:

Formula:

=IFERROR(MATCH(ROWS($1:1),Clients!H:H,0),"-")

This finds the row where the first matching record occurs on the Clients sheet, and when it is copied down it finds the row for the second matching record, then the third, and so on. Column H on both sheets can be hidden if you wish.

This formula is in cell A4:

Formula:

=IF(OR($H4="",$H4="-"),"",INDEX(Clients!B:B,$H4))

and it will return the Project Name for the first matching record. The formula can be copied across into B4:D4 to return the other information for the first matching record.

Finally, all those formulae in row 4 can be copied down as far as you think you need them - again, the hyphens indicate how far.

Now you can just choose a client in cell B1 and the data will adjust automatically.

Hope this helps.

Pete

Hi Pete, that is literally fantastic, thank you so much for the explanation and for the attached example. That is exactly what I wanted. Really appreciate your help.

Steve

Hi Pete,

Sorry I have another question. I have created my spreadsheet now using the formulas you provided and it is great. Is there now anyway I can get a client logo to display on the Client Overview sheet once I have selected a client from the dropdown? The logo would be contained in the Clients worksheet.

Thanks,
Steve

Thank you, I tried doing the above but when I use the data validation by consultant I get a really long list, as a consultant could have worked on more than one client, and I'm not sure how to approach my spreadsheet with your method. I attached a sample file just to give you an idea. There is way more data than that.

Thank you for your help.

S
Sample Experience.xlsx

@sush23

You shouldn't ask questions in someone else's thread (see Forum Rules). Instead, post back in your own thread, which you can find here:

http://www.excelforum.com/excel-prog...31#post3259131

Pete