+ Reply to Thread
Results 1 to 7 of 7

Lookup in multiple tables

  1. #1
    Registered User
    Join Date
    07-10-2014
    Location
    Shanghai
    MS-Off Ver
    2013
    Posts
    3

    Lookup in multiple tables

    Hi all,

    I have an excel file which will help us to manage our tasks on weekly basis. The layout I will describe as followed: It basically has two sheets. One sheet is a sheet with multiple tables, each table represents a project, each project having multiple tasks. For the other sheet it will function as an overview of all the tasks together. This sheet we want because we want to be able to sort on priority of each tasks. Now I have two problems basically. The first problem is: I don't know how to search in multiple tables at the same time. I know how to search in one table and returning multiple values which I have done (check the excel file). I think it can be done with the CHOOSE function but I don't know how to input it inside my current formula:

    Please Login or Register  to view this content.
    The second problem I am facing is:
    The goal of the second sheet is to sort all the tasks by priority. In that way we can put all the emergency tasks on top regardless of which project. However, it seems to take over the filter from the first sheet. What happens is, if in the first sheet the table is sorted by due date this also happens for the second sheet. When on the second sheet sorting on a different way it does not have effect because the sorting from the source seems to "overrule" this.

    If we cannot sort separately in the sheet with all the tasks doing all of this is not necessary.

    I have tried it to explain it clearly as possible. I think looking at the excel file also will help to create the idea. Another thing, as I am new here.. This are basically two questions, is the way of working to ask them separately as well?


    I hope one of you can help me

    Thanks a lot in advance
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Lookup in multiple tables

    try below array formula in B12 of sheet Overview
    copy paste below in B12 then hold control shift together and then hit enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    drag to right and drag down
    apply the proper formatting to cells

    with this you can apply three criteria

    project priority and owner
    change the three and you will get the things
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    07-10-2014
    Location
    Shanghai
    MS-Off Ver
    2013
    Posts
    3

    Re: Lookup in multiple tables

    Thanks a lot for that, that brought me much close. No idea I just could say: go from B12 to B68. However, I am still facing one problem. It is nice to filter for example on only emergency tasks but in case we want an overview of all the tasks this is not possible because we don't have a criteria for that. My first thought was applying an "*" but that did not do the case, what I did was:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Lookup in multiple tables

    Try below in Overview sheet sheet in B12
    should be entered as array formula(confirm with control shift and enter)\


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    drag down and Drag to right

    when you want to check the overview of all priorities, just keep priority of task (which is D3) blank!

    Hope this helps
    Last edited by hemesh; 07-15-2014 at 02:37 AM.

  5. #5
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Lookup in multiple tables

    hi hamesh ,

    Really great i have been trying to solve this using names range, Indirect etc but no use, since morning .

    Punnam

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Lookup in multiple tables

    Thanks for the Rep Punnam

  7. #7
    Registered User
    Join Date
    07-10-2014
    Location
    Shanghai
    MS-Off Ver
    2013
    Posts
    3

    Re: Lookup in multiple tables

    Nice my question also helped other people so thank you a lot for this. Quite amazing what excel can do in the right hands

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 05-10-2012, 10:38 AM
  2. Multiple tables lookup
    By tornado1981 in forum Excel General
    Replies: 3
    Last Post: 04-04-2010, 10:46 AM
  3. multiple lookup tables
    By ministerofdeath in forum Excel General
    Replies: 1
    Last Post: 03-24-2009, 03:52 AM
  4. Lookup tables with multiple columns
    By sharkfoot in forum Excel General
    Replies: 5
    Last Post: 03-30-2006, 10:48 AM
  5. Using Multiple LOOKUP tables
    By KG in forum Excel General
    Replies: 3
    Last Post: 05-06-2005, 08:06 PM

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