Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Post VLookup, or Index and NestedIF's

    I have 2 spreadsheets one that is retrieving data from a database (Vlookupdata)
    and the second spreadsheet (Filter workbook) I want to use to retrieve data from
    the first workbook. I want the second workbook to retrieve data based on the
    value in the List in cell B1. The data in the first workbook column AE is what
    determines what data is retrieved. Based on the values in the List "B-Pass" for
    example is for all the data greater than or equal to 600, NCY is for all data
    between the values of 528 - 60, HYR is for the data with the values between 400
    - 527, A pass is for data less than or equal to 399, and All is for all data. I
    am struggling to figure out how to do the Vlookup and the If statements with the
    ranges. If anybody can give me some help it would be greatly
    appreciated.

    It occurs to me that the Index Function may also be a way to
    do this but I can't quite figure that out.


    Thank you,

    Dan

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Welcome to the Lounge.

    A sample workbook would help.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    ..and what Excel version are you using???
    (The new Excel2016 has a new =IFS(...) function

    zeddy

  4. #4
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Sample workbooks

    Quote Originally Posted by zeddy View Post
    ..and what Excel version are you using???
    (The new Excel2016 has a new =IFS(...) function

    zeddy
    Hi Zeddy,

    I have Excel 2010. I have attached a couple of sample workbooks that show what I am trying to do.

    Thank you,

    Dan
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I cannot tell from this what you're trying to get in the "Filter workbook" in B5.

    Unless you plan to fill the formula, ROW($A$1) is a 1, so why not use that single digit?

    At least for me, I need more specific info.

  6. The Following User Says Thank You to kweaver For This Useful Post:

    SavGDK (2016-04-05)

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Dan

    I agree with kweaver that it is a little confusing regarding what you are trying to get.

    Perhaps you could get what you want using Autofilters and macros. See attached workbook (you need to open it with macros enabled)

    So this is how I did it..
    I copied the block of data from columns [C] to [AE] from your posted file [T4 Test.xlsx] and pasted this block as values to the same location on [Sheet1] in the attached workbook. (This could be done via an 'import macro').

    On [Sheet3] I added a named range [block]

    On [Sheet1], I added a lookup formula in [AF4] and copied this formula down.
    The top 3 cells in column [AF] use the same formula to reference the drop-down selection of cell [B1] (this is required to make the autofilters work OK).

    I added a vba routine to execute an autofilter based on the drop-down value as selected in cell [B1]. This routine also hides all filter dropdown arrows in all columns (except you must have at least one, so I left column [AF] )

    I then added a change-event-macro for the code in [Sheet1] which runs the autofilter macro whenever the cell value in cell [B1] changes i.e. whenever you change the drop-down value in cell [B1]
    The matching records are then displayed.

    zeddy
    Attached Files Attached Files

  8. The Following User Says Thank You to zeddy For This Useful Post:

    SavGDK (2016-04-05)

  9. #7
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Kweaver and Zeddy,

    Thank you for the replies. I apologize for the poor description of what I was trying to accomplish. My original plan was that depending on what mode was chosen in the filter spreadsheet for example Hyr, it would then do a lookup in the T4 spreadsheet and import just the data that matches that criteria, same for any other chosen modes. My first attempt, bear in mind I am a newbie at this, did not work out well and I could not figure out how to get it to go to the other sheet and import just the chosen data.

    Zeddy your proposed solution is great I as trying to limit the amount of data imported each time to keep the spreadsheet size at a minimum. I appreciate the help very much.

    Thank you,

    Dan

  10. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Dan

    ..one way you can keep spreadsheet size to a minimum is to save Excel files in .xlsb format.

    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    SavGDK (2016-04-05)

  12. #9
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I will try that thank you.

    Dan

  13. #10
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Dan

    I agree with kweaver that it is a little confusing regarding what you are trying to get.

    Perhaps you could get what you want using Autofilters and macros. See attached workbook (you need to open it with macros enabled)

    So this is how I did it..
    I copied the block of data from columns [C] to [AE] from your posted file [T4 Test.xlsx] and pasted this block as values to the same location on [Sheet1] in the attached workbook. (This could be done via an 'import macro').



    zeddy

    Zeddy,

    I am just wondering the easiest way to create the import macro? Is there a function in excel that will do that or would it have to be done with VBA?

    thanks,
    Dan

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Dan

    There are several ways you could import data into the 'Filter workbook'.
    For example, you could use a Data Query method.
    It all depends on how consistent the source file is.
    For example, if data workbook has a the same name etc etc etc. and how automated you want the process to be.
    It might be simpler to use VBA, and allow the User to click a macro button to display a 'File Selection' browser, to point to and choose the source data workbook etc etc etc.

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    SavGDK (2016-04-07)

  16. #12
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Dan

    There are several ways you could import data into the 'Filter workbook'.
    For example, you could use a Data Query method.
    It all depends on how consistent the source file is.
    For example, if data workbook has a the same name etc etc etc. and how automated you want the process to be.
    It might be simpler to use VBA, and allow the User to click a macro button to display a 'File Selection' browser, to point to and choose the source data workbook etc etc etc.

    zeddy
    Thanks for the info.

    Dan

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •