+ Reply to Thread
Results 1 to 5 of 5

Shortcut to select data specified in lists

  1. #1
    Registered User
    Join Date
    06-05-2015
    Location
    Glasgow, Scotland
    MS-Off Ver
    2013
    Posts
    10

    Shortcut to select data specified in lists

    Hello!

    I am wandering if anyone can help provide a shortcut function / equation to select the correct range of data specified by drop down lists.

    In the workbook attached ("Data Sheet"), there is unique data for 12 different climate locations across 12 months of the year with 9 different wind turbines, giving the total number of possible iterations to be 1296. From the drop down lists in B1-B3, I want to be able to select any combination of the three variables and present the relevant data in B6-B29. Is there a "clever" way of doing this? I am contemplating writing the longest IF function in history, but from my very limited understanding of MATCH, VLOOKUP and similar functions I believe there may be a way to avoid the early onset of arthritis... I just don't know how to!

    If anyone can provide even just a small piece of advice or steer in the right direction, I would really appreciate it.

    Thanks in advance,

    Calum.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,467

    Re: Shortcut to select data specified in lists

    Try this file:

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

    Re: Shortcut to select data specified in lists

    Corrected formula ......

  4. #4
    Registered User
    Join Date
    06-05-2015
    Location
    Glasgow, Scotland
    MS-Off Ver
    2013
    Posts
    10

    Re: Shortcut to select data specified in lists

    Hi John,

    Thanks very much for your help, that corrected formula works perfectly!

    Calum

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,467

    Re: Shortcut to select data specified in lists

    For completeness I have added an ERROR condition as I noted that one of the turbine name did not match your drop-down list. I have also named the input cells A1 to A3 as it might make the formula more understandable and changed the ranges. If you don't want to use names just wrap the IFERROR round the existing formula.

    =IFERROR(INDEX($E$1:$FC$250,MATCH(Turbine,$E$1:$E$250,0)+ROW()-4,(MATCH(Location,$E$1:$FC$1,0)+(MATCH(CalMonth,$E$3:$P$3,0)-1))),"error")

+ 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: 1
    Last Post: 10-16-2014, 08:00 PM
  2. [SOLVED] select the relevant data for use from three lists
    By shaz0503 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2014, 12:16 AM
  3. [SOLVED] Select All Cells Shortcut
    By enfieldsteve in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-19-2013, 02:56 PM
  4. Shortcut to select all data on a worksheet?
    By SEMMatt in forum Excel General
    Replies: 7
    Last Post: 09-01-2011, 01:53 PM
  5. Replies: 2
    Last Post: 04-16-2009, 04:11 PM

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