Excel combo box

  • Thread starter Thread starter SPikey
  • Start date Start date
S

SPikey

Hai,
i'am using Office2000 for my project data. I have to
saperate files, 1 for list of my suppliers and another 1
is table for my database. I try to put all my supplier
detail list using combo box in excel, but failed.

My plan is to get detail of my supplier once i click or
key-in words inside 1box.

ANybody have idea to help me. Please email direct to me.

Thank you.

Spike
 
Hi

You can format the cell using Data.Validation.List (looking like a combo)
with the list source on different worksheet, when you define list's source
table as named range, and refer to this named range in data validation
list's source. With source list on separate workbook, I'm not sure it'l
work - especially when the workbook with list is closed. But here's a
solution which'll work:

When you have the list's source table in different workbook (p.e.
ListSource.xlc) on some sheet (p.e. MyList) in column A, with list header in
A1, the in workbook with your database create a empty sheet p.e. MyList.
Open the table ListSource, and copy the cell A1 on sheet MyList. Activate
the sheet MyList in workbook with your database, and PasteSpecial.Link into
cell A1 on it. You get the formula
=[ListSource.xls]MyList!A1
Edit the formula in A1
=IF([ListSource.xls]MyList!A1="","";[ListSource.xls]MyList!A1)
and copy the formula down as much as you think you need - at least for same
nuber of rows as data in original list. Close the source workbook.

(When you had the sheet (MyList) with list source in same workbook with your
database, or you implemented it into it now, then you can skip the part
above and you can start right from here)

Define the named range p.e.
MyList=OFFSET(MyList!$A$2,,,COUNTIF(MyList!$A:$A,"<>")-1,1)
Select the range in your database, you want the combos into, and then
Data.Validation.List, with Source=MyList
 
Back
Top