|
|
|
|
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
|