making prices appear automatically when a value is selected

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

phill - 21 Apr 2006 11:14 GMT

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?

Reply to this Message

paul - 21 Apr 2006 11:51 GMT

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)

Signature

paul
paul.shepherd@nospamparadise.net.nz
remove nospam for email addy!

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

Reply to this Message

CYNTHIA - 22 Mar 2008 07:28 GMT

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

> 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
[quoted text clipped - 7 lines]
> > inputting materials and need the cell next to this to automatically put the
> > correct price in for each material used. can this be done?

Reply to this Message

Dave Peterson - 22 Mar 2008 15:01 GMT

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.

> I made a validation list and I want the value to pop up automatically.
>
[quoted text clipped - 34 lines]
> > > inputting materials and need the cell next to this to automatically put the
> > > correct price in for each material used. can this be done?

Signature

Dave Peterson

Reply to this Message

CYNTHIA - 23 Mar 2008 05:12 GMT

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

> Just a guess...
>
[quoted text clipped - 50 lines]
> > > > inputting materials and need the cell next to this to automatically put the
> > > > correct price in for each material used. can this be done?

Reply to this Message

Dave Peterson - 23 Mar 2008 13:59 GMT

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

> Hi,
>
[quoted text clipped - 65 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

CYNTHIA - 26 Mar 2008 04:36 GMT

No it worked.

Thanks for your help

> I'm not sure why you used ctrl-spacebar--didn't that just select the column?
>
[quoted text clipped - 72 lines]
> > >
> > > Dave Peterson

Reply to this Message