Excel dynamic list query

  • Hi everyone,


    I'm having some trouble coming up with a formula. I have a giant list of books in sheet1, which contains a number of columns: date, book name, language etc


    I then have a number of tabs for different languages (English, French, German etc)


    I would like to have formulas in the individual tabs that reference sheet1, but only include books in that language. So the French tab would only list the books from Sheet1 that are in French. The formula would have to be dynamic to account for new additions, and ignore new additions that are added for other languages.


    I'm assuming this would have to be some sort of MATCH/IF array formula, but I can't work it out.


    Thanks for your help.
    Vic.

  • Re: Excel dynamic list query


    Vic,


    In sheet1, I put Language in column A and Book title in column B.


    For example:


    A B
    1 French French1
    2 Spanish Spanish1
    3 French French2


    I then made a tab named French. This sheet has one column, A, named Book.
    The result should be:


    A
    1 Title
    2 French1
    3 French2


    There are two ways to approach the reference to "French." The first is to type it in a cell and reference it. The second is to use a formula to find the name of the worksheet. Which you choose is your preference.


    Reference method:
    I typed French into cell E1. In cells A2:A15, I used the following formula:
    {=IFERROR(INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$A$1:$A$15=French!$E$1,ROW(Sheet1!$B$1:$B$15),"Finished"),ROW()-1)),"")}


    The brackets around the formula indicate that it is an array formula. To enter one of these, enter everything but the brackets. Then, when you are done entering the formula, hold down control + shift and then press enter. That will tell Excel that you are using an array.


    The formula breaks down as follows:


    IFERROR - tells Excel to suppress errors (since you will want to copy this formula down, this is an easy way to do it).
    INDEX - finds the cell in the array
    SMALL - dictates which position in the array to return
    IF... - creates the array of French books by comparing the language in sheet1 for each line to the reference language. If they match, it records the row of that record in the array.
    ROW() -1 - Tells the small formula what position to use, based on the row in the French tab that the formula is on. Since I referenced the array from A1, I need to subtract one to compensate for the data starting in A2


    Formula Method:
    {=IFERROR(INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$A$1:$A$15=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256),ROW(Sheet1!$B$1:$B$15),"Finished"),ROW()-1)),"")}


    The breakdown is the same, except that
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
    is used to find the sheet name. There is some parsing going on in it to find just the sheet, since otherwise, it will bring back the entire path.


    To use this formula:
    I ran my formulas down 15 lines. Just change 15 to whatever number you want. Be sure to do it for all instances of 15. Then, copy the array and paste it down as many times as you like. A special note: you can't copy and then paste an array over itself. Excel will tell you that you are trying to change part of an array. So, just copy A1, then select A2:Awhatever you want, and paste the array. When pasting arrays, you do not need to use the control + shift + enter method.


    I hope this gets you what you need.

    Regards,


    WidgetWonka
    Puuuureeee Imagination

  • Re: Excel dynamic list query


    Assuming Excel 2007 . . .


    1. Add a helper column, header= Look up, on sheet1. Formula: =D2&COUNTIF(D$2:D2,D2), copied down the column
    2. Convert the the data range into an Excel Table.
    3. On the German, French, etc sheets use this formula to pull in the appropriate books:
    =INDEX(Table1,MATCH("German"&ROW()-1,Table1[Look up],0),COLUMN())


    See attached example.


    forum.ozgrid.com/index.php?attachment/42618/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!