'dynamic' drop down

You need to better explain what you are trying to achieve, that data makes no obvious sense.

Sorry Bob.
What I am trying to do is be able to use Data Validation (in cell G7). In colum A there are a bunch of names, some of them used more than once. In cell G7 I want a drop down of these names, however I only want each name to be available once in the data validation cell (I am antisapating using this on a large list where manufacture names will have hundreds of entries. If each manufacturer (name) was available for each record it would require considerable scrolling in the data validation cell).
Column B on sheet 1 has formulas (that I found on the web), that looks at the names in colum A and will only list each name once. Cell G7 then looks at this list for the available items for the data validation, thereby allowing each name to only be available once in the data validation cell (G7).
Problem is when the formulas are moved as on Sheet 2.
Any ideas?

Change the formula in Sheet1!B1 to

=IF(SUMPRODUCT(--(LEN($B$1:$B1)>0))=SUMPRODUCT(($A$1:$A$20<>"")/COUNTIF($A$1:$A$20,$A$1:$A$20&"")),"",OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1))

and copy down.

Use Formulas>Defined Names>NameManager to create a defined name, say called namesList with a RefersTo formula of

=OFFSET(Sheet1!$B$1,0,0,SUMPRODUCT(--(LEN(Sheet1!$B$1:$B$20)>0)),1)

Then in the Data Validation on Sheet2, use a formula of

=namesList

Thanks Bob, however when I copy the formula into Sheet1!B1 i get '0' in all the cells that I copied the formula into.
Any ideas?

Originally Posted by Cidona

Thanks Bob, however when I copy the formula into Sheet1!B1 i get '0' in all the cells that I copied the formula into.
Any ideas?

You need to array-enter it.

Marvin,
I'm sure in the right hands the advanced filter you mention could do it, however I will have to check that out another time. Was really looking for a quick fix based on the formula I posted which seems to have what I want but just needs tweeking.

Bob, I have entered that as an array (ctrl+shift+enter) and it put the curvy brackets in the formula, however it is still replying '0' in all the cells.

Here is your workbook with that formula working

That works great Haseeb. Thank you!!

Thank you again Bob and Marvin, I'm sure we'd have gotten your methods working also, but Haseebs is allowing me (novice user) to get there 'out of the box'.

Thanks again.

Bob,

You had posted with the working workbook inbetween my seeing Haseeb's reply and your posting.
Your method is working also, so thank you very much for the working formula.