+ Reply to Thread
Results 1 to 7 of 7

Index/Match and indirect

  1. #1
    Registered User
    Join Date
    12-16-2014
    Location
    Somerset, England
    MS-Off Ver
    2010
    Posts
    49

    Index/Match and indirect

    Hello,

    I am trying to pull information from numerous sheets in to one place depending on the month. eg: you select the month Jan 18 from a dropdown and it will then populate the data in the main data sheet.

    It is somewhat similar to this https://www.excelforum.com/excel-for...-tab-name.html however this isn't quite what I want.

    I need it to search by the Month selected in the dropdown, Event Action and Event Label. It would then populate the information from the total events under each heading on the main data sheet. I know it should use Match/Index and everywhere I have looked it suggest INDIRECT also, but teh examples given don't make any sense to me.

    Is it possible someone can help?

    I have included an example.
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,373

    Re: Index/Match and indirect

    For instance (be warned that indirect is volatile function and, if used too frequently in a sheet, can slow down recalculations)
    1) use the same heading in your table (B3) as used in data sheets: Telephone not Phone
    2) B4 and copy right/down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3) D4 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,834

    Re: Index/Match and indirect

    Try

    in F4

    =SUMIFS(INDIRECT("'"&TEXT($A$2,"mmm yy") &"'!$D:$D"),INDIRECT("'"&TEXT($A$2,"mmm yy") &"'!$A:$A"),$A4,INDIRECT("'"&TEXT($A$2,"mmm yy") &"'!$C:$C"),F$3)

    Copy across and down

  4. #4
    Registered User
    Join Date
    12-16-2014
    Location
    Somerset, England
    MS-Off Ver
    2010
    Posts
    49

    Re: Index/Match and indirect

    Hi Kaper,

    Thanks for the prompt reply. Unfortunately it doesn't like it and just shows #REF!

    Example included.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-16-2014
    Location
    Somerset, England
    MS-Off Ver
    2010
    Posts
    49

    Re: Index/Match and indirect

    Hi John.

    That does work and serves my purpose perfectly.

    As always, thanks.

    Suede

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,834

    Re: Index/Match and indirect

    The #REF is caused by assuming date in A2 is TEXT (rather than it being a date): hence conversion to TEXT in formula I supplied for F & G

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,834

    Re: Index/Match and indirect

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Instead of INDIRECT() need to have INDEX and MATCH
    By Michael9999 in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 03-28-2017, 05:58 AM
  2. [SOLVED] Index - match - indirect
    By JAYZE in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-30-2016, 12:09 PM
  3. Help with Index, indirect, and match
    By chachacharest in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-08-2014, 05:08 PM
  4. Index? Indirect? Match?
    By CpnVenice in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2013, 02:43 PM
  5. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM
  6. Index/Indirect/Match Help
    By robertsclark in forum Excel General
    Replies: 2
    Last Post: 04-20-2011, 10:24 AM
  7. Index, Indirect, Match
    By ruslan_adx in forum Excel General
    Replies: 1
    Last Post: 10-08-2009, 03:35 AM

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