Problem with Lists

[all4excel]

I trying to make a glossary for my Call-Center Study in Alphabetic way ...
Let me tel u that i owe a lot to this forum and all the moderators and all others..
I presently have two tabs i.e A-A ,B-C and one Tab for List and one Tab for Searching for a particular word..

I have defined three Names by Inserting ->Name ->Define..
AATerm--(formula-OFFSET('A-A'!$B$2,0,0,COUNTA('A-A'!$B:$B),1)
However the above formula does not work in the tab search when i select
A-A in the Dropdown-Index as i want the list till the last entry in the Col B in the Tab A-A....

* this formula works for B-C though
BCTerm--(formula- 'B-C'!$B$2:$B$1000 )
but i dont want to use it this way as it limits me to only 999 entries.

INDEX --(formula- List!$B$2:$B$9 )

In the search tab if u check the data validation i have used the Indirect Function to get a different Dropdown depending on the selection in the cell A2 in search tab the formula is (INDIRECT(LEFT(B2,1)&RIGHT(B2,1)&"Term"))

For strange reasons it works for B-C but not for A-A..whats the solution

i have attached the file plz suggest something i have tried everything..

[rylo]

Hi

As far as I can see, you can use indirect in the validation when the list has a fixed range (as per your B-C entry), or you can have a dynamic list created (as per your A-A) example, but you must use a fixed name in the data validation. It is possible to create a third dynamic list and have a fixed name on that list, then use validation to access that fixed name. However, I don't know how that would work in your situation where you want a different selection in each cell.

Easist thing I can think of is to use an event macro to delete / create the validation for each cell as you select it. So if you change your B-C entry to be dynamic as per the A-A then try the event

Please Login or Register  to view this content.

you should get going. This code isn't extensive - it only adds the list to column C. Needs to be refined to not use C1, and delete if the entry in column B is removed.

HTH

rylo

[rylo]

Hi

Had another go and managed to get it working with formulas. See how this goes.

rylo

[all4excel]

i t really worked very well and i am just trying to break the code in smaller parts to understand it very well...
have never used the search function.......
Thanx once again...