Excel formulation to automate values

[mns]

Hi there,
I have a little problem on arranging a small multi-worksheet excel here.
Let me explain in a short way then give some details on it.
I am trying to make an offers recordsheet which has two worksheets in it. One
for products(and their base prices) and an offer sheet. I would like to use
a bit dynamic data here. So when I type the name of the product excel would
give me the price from other worksheet.

in details;
Prices worksheet is something like:
A B
1 Product BasePrice
2 mouse 5
3 keyboard 8
4 ...

And the Offers sheet is like:

A B C D
1 OFFER1 blank blank blank
2 Product BasePrice Quantity Cost
3 mouse 5 2 10
4 keyboard 8 4 32
5 TOTAL 42

when i type mouse in A column I'd like excel to fill the BasePrice value
from Prices worksheet and when I change the value it keep updating the prices
on Offers worksheet.

Not sure if we need a macro to achieve this or not. Tried it with name
things, tried some vlookup but as you can guess I am stuck here. I'd be
thankful if someone could give me a hand here.

Thank you

 

[Pete_UK]

Put this in B3 of the Offer sheet:

=IF(A3="","",VLOOKUP(A3,Prices!A:B,2,0))

and in D3 you can put:

=IF(OR(B3="",C3=""),"",B3*C3)

Then you can copy these down as far as you need.

I've assumed that you will type a valid product name in column A, but
if you don't then you will get #N/A.

Hope this helps.

Pete

 

[Rad131304]

You want to use VLOOKUP; I think you would put the following formula in the B
column of the Offers sheet:

=VLOOKUP($A2,'Prices Worksheet'!$A:$B,2,FALSE)

This would look up the value in $A2 in the A column of the Prices Worksheet
and insert the associated value from the (2) second column of the specified
range A:B. The FALSE forces an exact match.



As sort of an aside/follw-on, you may want to use Data validation on the A
column in the Offers Worksheet. You would enforce list validation and point
the list source to the A column in the Prices Worksheet. The way to do this
changes depending on your version of Excel, so MS help is probably your best
bet for finding this.

Finally, lock the prices worksheet when you are done with it so it cannot be
modified.

Hope that helps.

 

[mns]

what do you mean by locking the prices?

You want to use VLOOKUP; I think you would put the following formula in the B
column of the Offers sheet:

=VLOOKUP($A2,'Prices Worksheet'!$A:$B,2,FALSE)

This would look up the value in $A2 in the A column of the Prices Worksheet
and insert the associated value from the (2) second column of the specified
range A:B. The FALSE forces an exact match.



As sort of an aside/follw-on, you may want to use Data validation on the A
column in the Offers Worksheet. You would enforce list validation and point
the list source to the A column in the Prices Worksheet. The way to do this
changes depending on your version of Excel, so MS help is probably your best
bet for finding this.

Finally, lock the prices worksheet when you are done with it so it cannot be
modified.

Hope that helps.