Hi
I am new to this forum. Please excuse any mistakes in etiquette etc.
I am setting up a form to select a list of vendors and have used an active x combo box as a list. I have linked that list to a named range "Vendors".
That named range is linked to a table. I have used a table so the list is dynamic, to enable future extension of that list by adding new vendors.
I have used the following formula in the "Vendors" named range =Lists!$B$2:INDEX(Lists!$B:$B,COUNTA(Lists!$B:$B,1))
Note: $B$2 is the 1st cell in the table range, but that cell is also BLANK. Not sure if this is relevant or not, as I know COUNTA generally ignores blanks. However, I need a blank in the drop down so initially the form has blank cells, prior to a user populating.
The issue I have is that the drop down list keeps adding a couple of random Vendor's names at the very bottom of the list (those vendors already exist higher up in the list). It is rather bizarre and I can't fathom why? The random names vary also, if I activate the list and scroll up and down those random names change to 2 other names? Perhaps it is my formula? But all I need to do is have a dynamic drop down, which increases as new vendors are added to the list/table.
Any help would be greatly appreciated.
Many thanks
I am new to this forum. Please excuse any mistakes in etiquette etc.
I am setting up a form to select a list of vendors and have used an active x combo box as a list. I have linked that list to a named range "Vendors".
That named range is linked to a table. I have used a table so the list is dynamic, to enable future extension of that list by adding new vendors.
I have used the following formula in the "Vendors" named range =Lists!$B$2:INDEX(Lists!$B:$B,COUNTA(Lists!$B:$B,1))
Note: $B$2 is the 1st cell in the table range, but that cell is also BLANK. Not sure if this is relevant or not, as I know COUNTA generally ignores blanks. However, I need a blank in the drop down so initially the form has blank cells, prior to a user populating.
The issue I have is that the drop down list keeps adding a couple of random Vendor's names at the very bottom of the list (those vendors already exist higher up in the list). It is rather bizarre and I can't fathom why? The random names vary also, if I activate the list and scroll up and down those random names change to 2 other names? Perhaps it is my formula? But all I need to do is have a dynamic drop down, which increases as new vendors are added to the list/table.
Any help would be greatly appreciated.
Many thanks