Combo or Drop Boxes?Advice?Help?

[Mayk]

I need this for my IT project.
I have a spreadsheet with list of clients and its types. Names starts at B7 and by running macro i made new clients can be added to the row.
What I am confused with now is that i have other spreadsheet wit invoices where heading is MAINCONTRACTOR and its begin at cell C4.
What i am looking for is that i want to make combo box which will have on that invoice spreadsheet list of clients which is found in the previous clients spreadsheet,the ones already in and it should also be added automatically new clients when added so i can choose in a column whoever contractor i want.
Anyone willing to help?
Please

[Aladin Akyurek]

On 2002-03-31 16:12, Mayk wrote:
I need this for my IT project.
I have a spreadsheet with list of clients and its types. Names starts at B7 and by running macro i made new clients can be added to the row.
What I am confused with now is that i have other spreadsheet wit invoices where heading is MAINCONTRACTOR and its begin at cell C4.
What i am looking for is that i want to make combo box which will have on that invoice spreadsheet list of clients which is found in the previous clients spreadsheet,the ones already in and it should also be added automatically new clients when added so i can choose in a column whoever contractor i want.
Anyone willing to help?
Please

Lets say that the worksheet containing client names is called Data.

Activate Insert|Name|Define.
Enter EndRow as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=MATCH(REPT("z",40),Data!$B:$B)

Activate Add.

Enter Clients as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=OFFSET(Data!$B$7,0,0,EndRow-6,1)

Activate OK.

Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.

Aladin

[Mayk]

[quote]
On 2002-03-31 16:27, Aladin Akyurek wrote:

On 2002-03-31 16:12, Mayk wrote:
I need this for my IT project.
I have a spreadsheet with list of clients and its types. Names starts at B7 and by running macro i made new clients can be added to the row.
What I am confused with now is that i have other spreadsheet wit invoices where heading is MAINCONTRACTOR and its begin at cell C4.
What i am looking for is that i want to make combo box which will have on that invoice spreadsheet list of clients which is found in the previous clients spreadsheet,the ones already in and it should also be added automatically new clients when added so i can choose in a column whoever contractor i want.
Anyone willing to help?
Please

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Lets say that the worksheet containing client names is called Data.

Activate Insert|Name|Define.
Enter EndRow as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=MATCH(REPT("z",40),Data!$B:$B)

Activate Add.

Enter Clients as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=OFFSET(Data!$B$7,0,0,EndRow-6,1)

Activate OK.

Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.

Aladin
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Aladin thnx 4 help
but i have few misunderstandings. Where should i type the formulas?macros box or formula box with = sign? i am not quite sure.
Also "Activate Insert|Name|Define." i dont really know what i shoul do in this step?r u able to explain more?plz
"Lets say that the worksheet containing client names is called Data."dont know wot dat is either.plz help a bit more if u get a time...
Thank you

[Aladin Akyurek]

Mayk,

My understanding is that you have the names of the clients in some worksheet. What is the name of this worksheet: Sheet1 or what?

You seem to have a macro that enables you to add new client names to the already existing list of names. Right?

You have also another worksheet that you use to set up an invoice. And, you want to be able to select from either a dropdown list or a ComboBox the name of the relevant client. Right?

Addendum: Is this a school project, I mean, an assignment?

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-01 03:32 ]

[Mayk]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Mayk,

My understanding is that you have the names of the clients in some worksheet. What is the name of this worksheet: Sheet1 or what?

You seem to have a macro that enables you to add new client names to the already existing list of names. Right?

You have also another worksheet that you use to set up an invoice. And, you want to be able to select from either a dropdown list or a ComboBox the name of the relevant client. Right?

Addendum: Is this a school project, I mean, an assignment?

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-01 03:32 ]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Aladin

sorry for not being online for a while and keep you waiting. Firstly yes ure right this is a project in excel.

What Ive done is a made a seperate spreadsheet from the main one, so there are two spreadsheets:
1/ As~project- main one
2/DATABASE~4~COMBO- database one

In DATABASE~4~COMBO spreadsheet i have a data of clients,it starts in a row B7.
then I made a button which allows me to enter new client under lat one entered.
What i need now is to make a combo box in As~project spreadsheet which will import clients names from DATABASE~4~COMBO spreadsheet.
What I dunno is that i dont know where and maybe how do i enter this commands u gave it to me before. like the ones below.Plz if u can help or give me a piece of small advice.
Thanx

Activate Insert|Name|Define.
Enter EndRow as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=MATCH(REPT("z",40),Data!$B:$B)

Activate Add.

Enter Clients as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=OFFSET(Data!$B$7,0,0,EndRow-6,1)

Activate OK.

Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Aladin Akyurek]

On 2002-04-04 11:41, Mayk wrote:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Mayk,

My understanding is that you have the names of the clients in some worksheet. What is the name of this worksheet: Sheet1 or what?

You seem to have a macro that enables you to add new client names to the already existing list of names. Right?

You have also another worksheet that you use to set up an invoice. And, you want to be able to select from either a dropdown list or a ComboBox the name of the relevant client. Right?

Addendum: Is this a school project, I mean, an assignment?

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-01 03:32 ]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Aladin

sorry for not being online for a while and keep you waiting. Firstly yes ure right this is a project in excel.

What Ive done is a made a seperate spreadsheet from the main one, so there are two spreadsheets:
1/ As~project- main one
2/DATABASE~4~COMBO- database one

In DATABASE~4~COMBO spreadsheet i have a data of clients,it starts in a row B7.
then I made a button which allows me to enter new client under lat one entered.
What i need now is to make a combo box in As~project spreadsheet which will import clients names from DATABASE~4~COMBO spreadsheet.
What I dunno is that i dont know where and maybe how do i enter this commands u gave it to me before. like the ones below.Plz if u can help or give me a piece of small advice.
Thanx

Activate Insert|Name|Define.
Enter EndRow as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=MATCH(REPT("z",40),Data!$B:$B)

Activate Add.

Enter Clients as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=OFFSET(Data!$B$7,0,0,EndRow-6,1)

Activate OK.

Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Activate Insert|Name|Define means:

Go to the Menu bar of Excel, choose Insert, choose Name in the Insert menu, and choose Define in the Name menu. After this, you end up in a window. Then carry out the rest of the instructions.

[Mayk]

Activate Insert|Name|Define.
Enter EndRow as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=MATCH(REPT("z",40),Data!$B:$B)

Activate Add.

Enter Clients as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=OFFSET(Data!$B$7,0,0,EndRow-6,1)

Activate OK.

Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Aladin i done what u ve told me however when i highlite the combobox and
Activate Insert|Name|Define then i typed all the formulas u told me to.
However at the end at last step when i activate OK and come back to invoice sheet where combo box was ,there is no data there?
am i doing something wrong?

[Ian Mac]

On 2002-04-05 03:15, Mayk wrote:

Activate Insert|Name|Define.
Enter EndRow as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=MATCH(REPT("z",40),Data!$B:$B)

Activate Add.

Enter Clients as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=OFFSET(Data!$B$7,0,0,EndRow-6,1)

Activate OK.

Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Aladin i done what u ve told me however when i highlite the combobox and
Activate Insert|Name|Define then i typed all the formulas u told me to.
However at the end at last step when i activate OK and come back to invoice sheet where combo box was ,there is no data there?
am i doing something wrong?

Aladin's answer is fine for 1 Workbook but won't work from one to another.

In case your wondering though, by follow Aladin's instructions you've created an Dynamic range, in which you list is housed.

The two ways of using this list (but only within the same Workbook) are:

1) Select the cell you want your list to be in,
Goto Data>Validation which brings up a dialogue box,
Click the Allow Combobox and select List.
Now, in the Source box type =Clients.
THis method is for a drop down box which will reveal itself when you select that cell.

2) For the Combobox, if your using the one from the Controls Toolbox list (it doesn't work ofr the Forms controls),
in design mode, Right Click on the button select Properties,
in the box next ListFill type =Clients.

Sorry I can't proporly answer your question, what (I think) you need is some VBA help, something I'm no Novice at.

[Ian Mac]

Just thought actually, in your Invoice book on a separate sheet called Data you could link the cells to the Client Workbook and follow Aladin's instruction in the Invoice Book instead.