+ Reply to Thread
Results 1 to 5 of 5

How to use INDEX formula in conjunction with Data Validation to output data.

  1. #1
    Registered User
    Join Date
    01-08-2014
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    How to use INDEX formula in conjunction with Data Validation to output data.

    Hi,

    I'm hoping someone can assist me with a problem I'm having with formula's in the attached spreadsheet.

    Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.

    Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).

    Also, the Data Validation dropdown includes blanks which I would like to exclude.

    Please note, I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.

    Thank you for your help!
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to use INDEX formula in conjunction with Data Validation to output data.

    Slight Addition in your formula

    In B3 Cell

    =INDEX(DATA!$A$3:$H$14,,MATCH($B$2,DATA!$A$1:$H$1,0)+COLUMN(A:A)-1)

    Drag it down and right...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to use INDEX formula in conjunction with Data Validation to output data.

    1) change the Data Validation definition in B2 to

    Pub 1, Pub 2

    note, no = sign, no semicolon.

    2) Put this formula in B3, then copy down/across the table:

    =OFFSET(INDEX(DATA!$A$1:$H$1, MATCH($B$2, DATA!$A$1:$H$1, 0)), ROWS($A$1:$A2), COLUMNS($A$1:A$1)-1, , )
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    01-08-2014
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: How to use INDEX formula in conjunction with Data Validation to output data.

    Thank you both; that worked perfectly!

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to use INDEX formula in conjunction with Data Validation to output data.

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a 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. How to use INDEX formula to output a cross section of data
    By Pope_003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2014, 12:02 AM
  2. [SOLVED] Dynamic data validation to determine the SUM related output?
    By SChalaev in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-11-2013, 02:15 AM
  3. Replies: 10
    Last Post: 11-03-2013, 06:53 PM
  4. Data Validation List Using Index & Match Formula
    By rajeev.raj in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-16-2012, 03:08 AM
  5. Replies: 5
    Last Post: 09-10-2010, 09:26 AM

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