Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formulation to automate values
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formulation to automate values
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 On Feb 18, 6:53*pm, mns wrote: * 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formulation to automate values
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formulation to automate values
what do you mean by locking the prices? :)
"Rad131304" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automate replace values in formula | Excel Discussion (Misc queries) | |||
If formulation: Is it possible... | Excel Discussion (Misc queries) | |||
If formulation | Excel Discussion (Misc queries) | |||
How to automate process of replacing values? | Excel Discussion (Misc queries) | |||
Time formulation? | Excel Discussion (Misc queries) |