I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?


"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson

Thanks I visited the website you spoke of and looks Imay have a winner!! -
Thanks so much for the help. I think I may be able to make this work now.


"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I made a typo in my formula back to you:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
should be:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$B$18,2,false)

You want to bring back the second column of the range. But A8:A18 only had one
column, hence the #ref! error.

And that 2 before the false means the 2nd column.

False means you want an exact match.

Debra's site explains this nicer.

Sorry about my typo.

John wrote:

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

OK very close now!!
Now the =if(a8="","",vlookup(a8,'calculation tables'!$a$8:$B$18,2,false)
I want to vlookup from range in col A9 and in col D9, I am also in the 4th
column on the calc tables sheet so I revised formula to
=IF(A8="","",VLOOKUP(A9,'calculation tables'!$A$9:$D$18,4,FALSE)) but is
returning a $0.00 where it should show $7.00. Where am I wrong? If using
non-adjacent columns (which I am), how do you tell that to the formula?


"John" wrote:

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Maybe it's your typo this time <bg.

=IF(A8="","",VLOOKUP(A9,'calculation tables'!$A$9:$D$18,4,FALSE))

You're checking A8, but using A9 in the =vlookup().

Maybe?????????



John wrote:

OK very close now!!
Now the =if(a8="","",vlookup(a8,'calculation tables'!$a$8:$B$18,2,false)
I want to vlookup from range in col A9 and in col D9, I am also in the 4th
column on the calc tables sheet so I revised formula to
=IF(A8="","",VLOOKUP(A9,'calculation tables'!$A$9:$D$18,4,FALSE)) but is
returning a $0.00 where it should show $7.00. Where am I wrong? If using
non-adjacent columns (which I am), how do you tell that to the formula?

"John" wrote:

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

A8 is where the validation box is located on the proposal sheet and A9 is
where the text starts on the calculation tables sheet. So I think the formula
is correct using those two items.
The first A8 is looking for the validation - correct?
The first A9 is looking for the beginning of the table correct? or is it to
be the same as A8?

"Dave Peterson" wrote:

Maybe it's your typo this time <bg.

=IF(A8="","",VLOOKUP(A9,'calculation tables'!$A$9:$D$18,4,FALSE))

You're checking A8, but using A9 in the =vlookup().

Maybe?????????



John wrote:

OK very close now!!
Now the =if(a8="","",vlookup(a8,'calculation tables'!$a$8:$B$18,2,false)
I want to vlookup from range in col A9 and in col D9, I am also in the 4th
column on the calc tables sheet so I revised formula to
=IF(A8="","",VLOOKUP(A9,'calculation tables'!$A$9:$D$18,4,FALSE)) but is
returning a $0.00 where it should show $7.00. Where am I wrong? If using
non-adjacent columns (which I am), how do you tell that to the formula?

"John" wrote:

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Got it to work!!! Thanks so much for your help it's greatly appreciated.
Now if I want an empty selection in validation to return a $0 or nothing at
all, how do I get formula to do that, I am getting a N/A which is OO, I just
added a N/A in the validation and gave it a $0 amount to return, but would
rather empty selection return an empty cell

"Dave Peterson" wrote:

I made a typo in my formula back to you:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
should be:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$B$18,2,false)

You want to bring back the second column of the range. But A8:A18 only had one
column, hence the #ref! error.

And that 2 before the false means the 2nd column.

False means you want an exact match.

Debra's site explains this nicer.

Sorry about my typo.

John wrote:

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You can't actually have an empty cell ('cause it'll have the formula in it), but
you can make it look blank.

And I'm not sure how you can get N/A's back. Doesn't your validation force the
user to choose one from the list???

Ahhh. You made your validation range larger than you currently need so that you
add more later -- but not have to screw around with the range name. But the
=if(a8="","",...) portion should have taken care of that.

I'm still confused about your #n/a's.

To stop the 0's from coming back when the cell is empty:

=if(a8="","",=if(vlookupformula="","",vlookupformu la))

=======

And (even though I'm confused), you can check for errors with something like:

=if(a1="",if(iserror(vlookupformula),"",
if(vlookupformula="","",vlookupformula)))

(all one cell)


====
Some more info if you data|validation list can grow.

I find it much easier to dedicate a sheet for this and start my lists in row 1
of a column. Then use that column for nothing more than that list. It makes
the dynamic range name much easier to use.

If you like this, it's off to Debra's site once more!
http://www.contextures.com/xlNames01.html#Dynamic


John wrote:

Got it to work!!! Thanks so much for your help it's greatly appreciated.
Now if I want an empty selection in validation to return a $0 or nothing at
all, how do I get formula to do that, I am getting a N/A which is OO, I just
added a N/A in the validation and gave it a $0 amount to return, but would
rather empty selection return an empty cell

"Dave Peterson" wrote:

I made a typo in my formula back to you:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
should be:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$B$18,2,false)

You want to bring back the second column of the range. But A8:A18 only had one
column, hence the #ref! error.

And that 2 before the false means the 2nd column.

False means you want an exact match.

Debra's site explains this nicer.

Sorry about my typo.

John wrote:

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Since you got it to work, I bet this can be ignored -- well, except for this
reply <bg.



John wrote:

A8 is where the validation box is located on the proposal sheet and A9 is
where the text starts on the calculation tables sheet. So I think the formula
is correct using those two items.
The first A8 is looking for the validation - correct?
The first A9 is looking for the beginning of the table correct? or is it to
be the same as A8?

"Dave Peterson" wrote:

Maybe it's your typo this time <bg.

=IF(A8="","",VLOOKUP(A9,'calculation tables'!$A$9:$D$18,4,FALSE))

You're checking A8, but using A9 in the =vlookup().

Maybe?????????



John wrote:

OK very close now!!
Now the =if(a8="","",vlookup(a8,'calculation tables'!$a$8:$B$18,2,false)
I want to vlookup from range in col A9 and in col D9, I am also in the 4th
column on the calc tables sheet so I revised formula to
=IF(A8="","",VLOOKUP(A9,'calculation tables'!$A$9:$D$18,4,FALSE)) but is
returning a $0.00 where it should show $7.00. Where am I wrong? If using
non-adjacent columns (which I am), how do you tell that to the formula?

"John" wrote:

Thanks Dave, I almost have it now. Here is what I have:
=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)
A8 is blank in the calculation tables sheet, and A9 is where the same text
starts (from the validation cell) on calc tables sheet and cost to use is
located in D9. I have tried to replace the
vlookup(a8,'calculation tables'!$A$8:$A$18,2,false)) with
vlookup(A9,'calculation tables'!$D$9:$D$18,2,false)) and keep getting a
#ref! sign.
Not sure where I am going wrong, any ideas. Also what does the #2 represent
in the calculation right before ,false?

"Dave Peterson" wrote:

First, if the data|validation list is on another worksheet, you have to give
that range a nice name. See Deb's site:
http://www.contextures.com/xlDataVal01.html#Create
(the second step, especially)

Then in your formula:

=if(a8="","",vlookup(a8,'calculation tables'!$a$8:$a$18,2,false)

Goes in C8 of the proposal sheet.

John wrote:

OK been trying to do the validation for the combo box replacement. I get the
name in the validation box, and I have created a models tab which has the
same names and the numbers i want associated with the validation. I am having
troubles with understanding where the scenarios and where to tell the
validation to look for the numbers associated with the text on the models
tab. Having troubles with the Vlookup formula to tell it where to look and
how to bring the numbers associated with the validation.

I have:

Validation in cell A8 on tab Proposal Worksheet

I have placed columns of information in the Calculation Tables sheet (tab at
bottom) - A8:A18 is the text I want in the Validation.
B8:B18 are the numbers I want returned to cell C8 on the Proposal Worksheet.
Do I need to create a scenario for this routine? and if so I do not
understand how scerio works and where to place the right information.

I understand a little bit of what she spoke about in the sample excel sheet,
but lost me on the scenarios and vlookup.

"Dave Peterson" wrote:

Yes, but is your combobox from the control toolbox toolbar or a dropdown from
the Forms toolbar.

Each behaves differently.

But after you try Data|Validation, you'll see that you'll want to use it.

One problem with Data|Validation: You can copy a cell and paste over the cell
with data|validation and it removes that validation.



John wrote:

Thanks, but have not used or heard of data validation. I will have to look
her up and delve a little deeper into my Excel Inside Out book. I think this
one I will need a little more help with. I will try what you suggested and
get back to you for anymore help I may need.

But can it be done with a combo box?

"Dave Peterson" wrote:

How about a slight alternative?

Instead of using a combobox, how about using Data|Validation.

You can set up another worksheet with the values to be chosen in the
data|validation dropdown in column A. Then put the associated price in column
B.

Say you put the data|validation cell in B8.

then in C8, you could use:

=if(b8="","",vlookup(b8,sheet2!a:b,2,false)

Debra Dalgleish has some nice notes about data|validation at:
http://www.contextures.com/xlDataVal01.html

And she has other nice notes about =vlookup() at:
http://www.contextures.com/xlFunctions02.html

John wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi

I would like to know how to compute the combo box & normal cell.

For example: -

drop down combo box value 20 x normal cell 10
= result cell

Pls kindly advice me

JOHN Kyaw

"John" wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

Assign a linked cell to that combobox and then just use regular excel.

=a1*a2

(where a1 is the linked cell and a2 is the "normal" cell.)

John wrote:

Hi

I would like to know how to compute the combo box & normal cell.

For example: -

drop down combo box value 20 x normal cell 10
= result cell

Pls kindly advice me

JOHN Kyaw

"John" wrote:

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

--

Dave Peterson