+ Reply to Thread
Results 1 to 6 of 6

Filtering Based on Fixed Value

  1. #1
    Registered User
    Join Date
    11-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    45

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Filtering Based on Fixed Value

    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: copy to clipboard
    Please Login or Register  to view this content.


    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: copy to clipboard
    Please Login or Register  to view this content.


    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: copy to clipboard
    Please Login or Register  to view this content.


    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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Filtering Based on Fixed Value

    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

  4. #4
    Registered User
    Join Date
    11-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Filtering Based on Fixed Value

    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

  5. #5
    Registered User
    Join Date
    08-09-2011
    Location
    Colorado Spgs, USA
    MS-Off Ver
    Excel 2008
    Posts
    34

    Question Re: Filtering Based on Fixed Value

    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

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Filtering Based on Fixed Value

    @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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1