I have a list of Territorial managers id's that are listed using simple
data validation. But when they pick their number from the validated
cell I need a combo box populated with only the customers that
territory manager has. I have data with over 2000 customers linked to
about 60 different TM#'s to select from so the cell needs to populate
and update automatically when new data is entered into the data sheet.
The data is on the "Data" sheet and the workbook is on sheet "Day".

This data need to fill 25 combo boxes (all with the same information,
even duplicates in each combo box) each in a different row. (We are
keeping a record of the Territorial manager's traveling for each
customer they meet with in a single month). The max amount of customers
they can visit is no more than 25, so if they actually met with 25
customers each customer is known.

I have a formula for variable strings that i posted earlier today, but
I found out that simple data validation in the excel cell can't handle
the excessive amount of data, and I receive 1004 runtime errors and
then sometimes it won't populate all the data into the cell, and cutoff
letters of customer's names.

Any solutions are welcomed, I am at the point where I am going to be
working on this project forever. thanks for any help

Jason


--
jasda2
------------------------------------------------------------------------
jasda2's Profile: http://www.excelforum.com/member.php...o&userid=23956
View this thread: http://www.excelforum.com/showthread...hreadid=375804

loop through you data and load an array with the information you need in the
combobox for each row that meets the TM#.

Assign the array to the list of each combobox.

Dim v as Variant
Dim i as Long

With worksheets("Data")
set rng = .Range(.Cells(2,"C"),.Cells(2,"C").end(xldown))
End with
redim v(1 to rng.count)
i = 0
for each cell in rng
if cell.value = Worksheets("Day").Range("A1") then
i = i + 1
v(i) = cell.offset(0,-2).Value
end if
Next
redim preserve v(1 to i)
for each oleObj on worksheets("Day").OleObjects
if typeof oleObj.Object is MSforms.combobox then
oleObj.Object.List = v
end if
Next

--
Regards,
Tom Ogilvy

"jasda2" wrote in
message ...

I have a list of Territorial managers id's that are listed using simple
data validation. But when they pick their number from the validated
cell I need a combo box populated with only the customers that
territory manager has. I have data with over 2000 customers linked to
about 60 different TM#'s to select from so the cell needs to populate
and update automatically when new data is entered into the data sheet.
The data is on the "Data" sheet and the workbook is on sheet "Day".

This data need to fill 25 combo boxes (all with the same information,
even duplicates in each combo box) each in a different row. (We are
keeping a record of the Territorial manager's traveling for each
customer they meet with in a single month). The max amount of customers
they can visit is no more than 25, so if they actually met with 25
customers each customer is known.

I have a formula for variable strings that i posted earlier today, but
I found out that simple data validation in the excel cell can't handle
the excessive amount of data, and I receive 1004 runtime errors and
then sometimes it won't populate all the data into the cell, and cutoff
letters of customer's names.

Any solutions are welcomed, I am at the point where I am going to be
working on this project forever. thanks for any help

Jason


--
jasda2
------------------------------------------------------------------------
jasda2's Profile:
http://www.excelforum.com/member.php...o&userid=23956
View this thread: http://www.excelforum.com/showthread...hreadid=375804

Can someone elaborate on to create a variable array based off m
conditions. The array neems to be built so when i select the TM nam
range (ie 0511,3111,2111, etc #) that the customers in the coordinat
with the Tm#.

Column 1 (TM#) Column 2 (customer name)
0511 dfsfgdffg
0511 sdafgdfg
0511 sddsfsda
0511 ssdafsdaf
0511 dsfsadf
0511 sdfsadfsdf
3111 dsfdsfsdf
3111 sdffdssdaf
3111 dsfsdfafds
3111 sadfsdadfs
3111 fdsfdsfds
2111 dfsfdsfds
2111 dfsfdsdfs
2111 sadffsadfds


Only thing I need is an array built like so...

0511
sadfdsfa
fdfgfdfgfddf
fdsag
sdfsdsadfgbadf
sdafdfsdasdsdfsd

then i can name the variable array/range so I can map it to anothe
drop down menu (validation or combo box, whichever is easier)..

--
jasda
-----------------------------------------------------------------------
jasda2's Profile: http://www.excelforum.com/member.php...fo&userid=2395
View this thread: http://www.excelforum.com/showthread.php?threadid=37580

Dim sTarget as String
Dim rng as Range, rng2 as Range
Dim rngStart as Range, rngEnd as Range
Dim cell as Range
sTarget = "0511"
With Worksheets("Data")
set rng = .Range(.cells(2,2),.cells(2,2).End(xldown)(2))
End With
set rngStart = nothing
for each cell in rng
if cell.Offset(0,-1).Text = sTarget then
if rngStart is nothing then
set rngStart = cell
end if
Elseif cell.offset(0,-1).Text < sTarget and _
not rngStart is nothing then
set rngEnd = cell.offset(-1,0)
exit for
end if
Next cell
set rng2 = Range(rngStart,rngEnd)
rng2.Name = "List1"
msgbox rng2.Address

--
Regards,
Tom Ogilvy


"jasda2" wrote in
message ...

Can someone elaborate on to create a variable array based off my
conditions. The array neems to be built so when i select the TM name
range (ie 0511,3111,2111, etc #) that the customers in the coordinate
with the Tm#.

Column 1 (TM#) Column 2 (customer name)
0511 dfsfgdffg
0511 sdafgdfg
0511 sddsfsda
0511 ssdafsdaf
0511 dsfsadf
0511 sdfsadfsdf
3111 dsfdsfsdf
3111 sdffdssdaf
3111 dsfsdfafds
3111 sadfsdadfs
3111 fdsfdsfds
2111 dfsfdsfds
2111 dfsfdsdfs
2111 sadffsadfds


Only thing I need is an array built like so...

0511
sadfdsfa
fdfgfdfgfddf
fdsag
sdfsdsadfgbadf
sdafdfsdasdsdfsd

then i can name the variable array/range so I can map it to another
drop down menu (validation or combo box, whichever is easier)...


--
jasda2
------------------------------------------------------------------------
jasda2's Profile:
http://www.excelforum.com/member.php...o&userid=23956
View this thread: http://www.excelforum.com/showthread...hreadid=375804

Tom I thank you for the replies, but I cannot seem to get any of th
code working. The TM# is the variable number that is pulled down fro
a validated list I have. This variable number is the number that need
to match whatever is the corresponding customer. So if 1157 has 5
customers, thos 50 customers listed will populate a field/array/lis
something that i can work with, but it needs to be variable. So i
1157# changes to 0511 then the list changes and fills with that number
corresponding customers. If the comboxbox is avoidable thats better, i
not it will be fine. But all the code you posted I'm lost what goe
where, and where I need to change it to fit my needs.

The numbers and customers are listed in ascending order with customer
matching. i.e.

1157 sadsda
1157 fgfgvfv
1157 dsdsdc
1157 xzdsffv
0511 vbdfdf
0511 sddsffsd
0511 ujujyujy

These are also on a different worksheet. The variable TM# is cel
$B$4. This is the number that changes.

Jaso

--
jasda
-----------------------------------------------------------------------
jasda2's Profile: http://www.excelforum.com/member.php...fo&userid=2395
View this thread: http://www.excelforum.com/showthread.php?threadid=37580