Excel dynamic list query

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.

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/