+ Reply to Thread
Results 1 to 6 of 6

How to combine IF and OFFSET/MATCH/COUNTIF together??

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Cowtown, AB
    MS-Off Ver
    2010
    Posts
    30

    Exclamation How to combine IF and OFFSET/MATCH/COUNTIF together??

    Hello,

    I have a problem and I don't know how to do it could someone please help me?
    The problem is I want to combine IF and OFFSET formula in Data Validation together to make one formula. The following link is from Chandoo and it is what I want to combine Please Click here

    I want to combine the formula in Problem 1 and Problem 2 together so that way I could have top 5 most used items by certain departments and also a full list when I click on it, could someone help me please?!?!

    Thank you so much!
    Kind Regards

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to combine IF and OFFSET/MATCH/COUNTIF together??

    Hi and welcome to the forum.

    Be sure that best way to describe your problem, is to upload YOUR sample workbook,so we can work on it!
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    05-29-2012
    Location
    Cowtown, AB
    MS-Off Ver
    2010
    Posts
    30

    Re: How to combine IF and OFFSET/MATCH/COUNTIF together??

    Hi Fotis1991,

    Sorry here is the uploaded file.

    This is a project I have to do for work and I was wondering if you guys can help me.

    Basically on Worksheet 1 is the data validation for IF function, the purpose of this function is to show the "Short list" once you select the drop down. The short list will consist of top 5 items for you to select e.g. a, b, c, x, y, if you select on the "Full list" then the drop down will change to "Full list" column where you can select from a-z. Note* I will be replacing them with task and such etc.

    On Worksheet 2 is the OFFSET function, once I select the "Select Area" drop box and chose the area for the task "Select Project" box will automatically only show the items for that category only (e.g. Marketing --> only show the 4 items from marketing)

    So my question is how do I combine the two formula from worksheet 1 and worksheet 2 together to make one formula where I click on the drop down box from "Select Area" then the "Select Project" and it will list 5 of my most common task for that area but also have the option for "Full List". This is because I will have about 20+ items for 1 area/category and I don't want to scroll around looking for the 5 most common items I use, I just want the 5 most items I use but also have the option of clicking on the "Full List" to show all.
    Attached Files Attached Files

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to combine IF and OFFSET/MATCH/COUNTIF together??

    Hi

    Take a look to the second Sheet.

    Is this what are you looking for?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-29-2012
    Location
    Cowtown, AB
    MS-Off Ver
    2010
    Posts
    30

    Re: How to combine IF and OFFSET/MATCH/COUNTIF together??

    Hi Fotis1991,

    It is similiar to what I am looking for except I want the "Full List" to be in the "Select Project" not Area. Say I have 2 commonly used items under marketing, I want those 2 to show but also have the option of "Full List" so when I click on "Full List" it will show the full list for only marketing and not the whole list for all the Areas.

    Please see my attached file, it will give you more of a sense of what I am working with. The file is a timesheet where I want to fill out the timesheet with task I've done through out the day. I want to be able to select the initials under "Name" then it will filter out the 5 most commonly used task for that person but also have "Full List" option, once I click that it will show all the task under that person and not the full listing because I will have over 100 tasks.

    If you could help me with this problem, much would be appreciated

    Kind Regards,



    Quote Originally Posted by Fotis1991 View Post
    Hi

    Take a look to the second Sheet.

    Is this what are you looking for?
    Attached Files Attached Files
    Last edited by xadieu; 05-29-2012 at 06:15 PM.

  6. #6
    Registered User
    Join Date
    05-29-2012
    Location
    Cowtown, AB
    MS-Off Ver
    2010
    Posts
    30

    Re: How to combine IF and OFFSET/MATCH/COUNTIF together??

    Bump.

    Can anyone help me with this?

    Thanks,

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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