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?

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

remove nospam for email addy!



"phill" wrote:

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?

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
"paul" wrote:

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

remove nospam for email addy!



"phill" wrote:

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?

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

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
"paul" wrote:

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

remove nospam for email addy!



"phill" wrote:

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?

--

Dave Peterson

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" wrote:

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

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
"paul" wrote:

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

remove nospam for email addy!



"phill" wrote:

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?

--

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

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" wrote:

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

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
"paul" wrote:

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

remove nospam for email addy!



"phill" wrote:

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?

--

Dave Peterson


--

Dave Peterson

No it worked.

Thanks for your help

"Dave Peterson" wrote:

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

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" wrote:

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

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
"paul" wrote:

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

remove nospam for email addy!



"phill" wrote:

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?

--

Dave Peterson


--

Dave Peterson