making prices appear automatically when a value is selected

[Guest]

hi

i need some help if possible. i have a spreadsheet that i have designed for
invoicing. i have a drop down list which i have used data validation for
inputting materials and need the cell next to this to automatically put the
correct price in for each material used. can this be done?

 

[Guest]

vlookup.Say your validation list returns your product in B2,and your pruduct
list is in c1:c50,with a price column next to it in d1:50.Your product list
is the same list that is the source for your drop down so the vlookup will
always be exact(a good thing for a price book)=vlookup( b2,C1:d50,2,false)

 

[CYNTHIA]

I made a validation list and I want the value to pop up automatically.

I have 2 worksheets Sheet 1 and 2.

In worksheet 2 has names and chart for prices.
I made a validation list from worksheet 2. Defined a name of Animals.

In worksheet 1 in columns.
A B
Cat $6.00
Dog $4.00
Fish $90.00
Rabbit $23.00

I pasted the validation list. I did a vlookup and I am getting an error
message N/A but some values pops up when I use this formula:
=VLOOKUP(A5,Sheet1!A5:B8,2,FALSE)
when i choose the option dog i get N/A but when I choose Rabbit I get the
correct price. I copy this to 50lines and same problem i choose an animal
and some prices does not comes up

Please help me

 

[Dave Peterson]

Just a guess...

I'm betting that when you copy the formula to the other cells, you're formula is
changing:

=VLOOKUP(A5,Sheet1!A5:B8,2,FALSE)
becomes
=VLOOKUP(A6,Sheet1!A6:B9,2,FALSE)
....
The look up range is changing.

To keep that portion of the formula absolute, you can use:
=VLOOKUP(A5,Sheet1!$A$5:$B$8,2,FALSE)

Now if you copy this formula, the $a$5:$b$8 portion won't change.

 

[CYNTHIA]

Hi,

I copy the formula to other cells it's cell changing. but very differently I
used the formula listed in
A5
=VLOOKUP(A5,Sheet1!A5:B8,2,FALSE)
but instead of this I used
=VLOOKUP(A5,Sheet1!$A$5:$B$8,2,FALSE)
and did ctrl+spacebar and when i choose form my validation list for example
for dog the value comes up as N/A

 

[Dave Peterson]

I'm not sure why you used ctrl-spacebar--didn't that just select the column?

I'd review the notes for troubleshooting =vlookup() formulas from Debra
Dalgleish:

http://contextures.com/xlFunctions02.html#Trouble

 

[CYNTHIA]

No it worked.

Thanks for your help

I'm not sure why you used ctrl-spacebar--didn't that just select the column?

I'd review the notes for troubleshooting =vlookup() formulas from Debra
Dalgleish:

http://contextures.com/xlFunctions02.html#Trouble