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

0
Utf
2/18/2010 6:53:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
456 Views

Similar Articles

[PageSpeed] 17

Put this in B3 of the Offer sheet:

=3DIF(A3=3D"","",VLOOKUP(A3,Prices!A:B,2,0))

and in D3 you can put:

=3DIF(OR(B3=3D"",C3=3D""),"",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=A0pm, mns <m...@discussions.microsoft.com> wrote:
> =A0 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 u=
se
> a bit dynamic data here. So when I type the name of the product excel wou=
ld
> give me the price from other worksheet.
>
> in details;
> Prices worksheet is something like:
> =A0 =A0 =A0 =A0A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 B
> 1 =A0 Product =A0 =A0 =A0 =A0BasePrice
> 2 =A0 =A0mouse =A0 =A0 =A0 =A0 =A0 =A05
> 3 =A0 =A0keyboard =A0 =A0 =A0 =A08
> 4 ...
>
> And the Offers sheet is like:
>
> =A0 =A0 =A0 =A0 A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 B =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0C =A0 =A0 =A0 =A0 =A0 =A0 =A0D =A0 =A0 =A0 =A0
> 1 =A0 OFFER1 =A0 =A0 =A0 =A0 blank =A0 =A0 =A0 =A0 =A0 =A0 blank =A0 =A0 =
=A0 =A0 blank
> 2 =A0 Product =A0 =A0 =A0 =A0BasePrice =A0 =A0 =A0Quantity =A0 =A0 =A0Cos=
t
> 3 =A0 =A0mouse =A0 =A0 =A0 =A0 =A0 =A05 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A02 =A0 =A0 =A0 =A0 =A0 =A0 10
> 4 =A0 =A0keyboard =A0 =A0 =A0 =A08 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A04 =
=A0 =A0 =A0 =A0 =A0 =A0 =A032
> 5 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 TOTAL =A0 =A0 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 pr=
ices
> 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

0
Pete_UK
2/18/2010 7:23:33 PM
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
> 
0
Utf
2/18/2010 7:36:01 PM
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
> > 
0
Utf
2/18/2010 8:01:02 PM
Reply:

Similar Artilces:

i cannot use the mouse right click in excel. how to rectify?
i cannot use the mouse right click in excel. how to rectify? Is this just when over the cells ? Does it work on the title bar - windows options Or on the Excel menus ? Steve On Tue, 19 Sep 2006 14:21:01 +0100, j <j@discussions.microsoft.com> wrot= e: > i cannot use the mouse right click in excel. how to rectify? Unless the right-click button is frozen, I'm sure you can use the mouse right-click in Excel Just hit the right-click button. Now...........................the real issue What are you right-clicking on and what doesn't happen for you? Does the right-click no...

Getting value from 2 cols right, 1 row down
Hi, I guess this is a simple question but I couldn't figure out how to ge around it. Code ------------------- A B C D E F 1 Worker 100 100 100 100 2 110 110 3 Worker 120 120 120 4 130 130 13 ------------------- Sheet above is a work schedule and 100, 110, 120 and 130 are jus predefined work shifts. Row 1 is a morning shift row and Row 2 is night shift row for the first worker. Now what I want to do is to create another sheet with workers names t m...

Word Automation Data Sources
Hi, My customer wants me to generate an invoice from data contained in MS Access. He has a Microsoft Word - based invoice that he currently uses and wants the new system to use the same document. So I intend to use Word Automation and MailMerge to meet the requirement. Some of the information on the invoice is name, address, city, state, zip - one record per invoice, no problem. But he also has a detailed section of the invoice that will contain multiple line items. So there will be multiple records per invoice for that part. Question: Can one Word document hav...

import hyperterminal value into excel
How do I import a hyperterminal value into an excel cell? ...

Excel Function to Access
I would like this excel function =IF(A4=A5,C5-C4,C5) to be utilized a a function in a query in MS Access where the fields that supposed t be in column A are the primary key fields and the next fields whic are in column C, are to be calculated in.The answers to this functio are to go into a new field.....Any help please? : Primary key, by definition, means no duplicates. How would A4 ever equal A5? -- George Nicholson Remove 'Junk' from return address. "CJT" <chico_2186@yahoo-dot-com.no-spam.invalid> wrote in message news:Wa-dnQuiOcaYskDfRVn_vQ@giganews.com... &g...

Is it possible to have 2 excel version in one computer?
hi everyone i just installed a 2003 version office... i liked the new features but when i wanted to open an excel file that has been created by 2002 version excel, i was shocked that i can't open the file successfully. When i opened and enabled the built in macros in the file, an error message popped out and said that the data may have been lost. After I have closed the error message, i checked the file. the data is still in the worksheet but all the words, format, buttons, and other information have all disappeared. However, if I open it in another version other than 2003, the file work...

Free MS Excel/Access Video Tutorials and Articles
Hey All, There are a whole bunch of free articles and video tutorials at http://www.davidbase.com. There's no cost to sign up and there are lots of articles and videos to check out. Some of the topics are Access VBA, Excel, Database Design, Reporting, Union Queries and more. Some recent articles: Access Parameter Queries Union Queries Excel VBA Random Numbers Relationships and Joins � and plenty more. Sign up (it's free) right on the home page and see if it suits you. Thanks, AppTrix Staff http://www.davidbase.com ...

Excel Formulae #3
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

Excel -- Wrapped text disappears
A user here is having the same problem as described in a previous topic (http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/b32b50c3e69981d/dc8adb6c01616d70%23dc8adb6c01616d70). Windows XP Pro, SP2 Office 2003 Pro, SP2 New machine with plenty of resources. Was originally working properly, then mysteriously developed a strange symptom: Any text that is in an Excel cell in which the "wrap text" option has been selected no longer shows up. I have checked it with 1 character, 1 word, and multiple lines of text. It has been repeated with spreadsheets created on...

EXCEL caused an invalid page fault #3
Whenever I start EXCEL 2000 I get the following msg. EXCEL caused an invalid page fault in module EXCEL.EXE at 018f:3001de79. Registers: EAX=900a0154 CS=018f EIP=3001de79 EFLGS=00010246 EBX=00100000 SS=0197 ESP=0062eb5c EBP=0062ebf0 ECX=0000c630 DS=0197 ESI=50020044 FS=0ebf EDX=30021a90 ES=0197 EDI=300378d4 GS=0000 Bytes at CS:EIP: 0f bf 04 c5 aa df 01 30 89 45 dc 8d 45 e4 50 e8 Stack dump: 50020044 006b76e4 006b7608 006b7608 0062ebf0 006b7608 006b76e4 006b7608 0062eb54 3001dcc3 00000000 bff728a2 bff54f53 1294bb9c ad4c17af 12990002 I had reinstalled and repaired several times but it does n...

Bug in excel 2003
Hi, I'm trying to get an equation of a table of data. When I enter the data, make a graph of it, I get a nice sinus-kind-of-line. Therefor I would like to have fourth order equation. The line of data looks very good (125 data points). Then I add a trendline (4th order) that looks like a very nice fit. But when I set the properties of the trendline to "show equation in graph" the a wrong equation is displayed. After a lot of fiddling with data, I noticed that the shown equation has wrong contstands showing. E.g.: The shown eqaution = A + B*X + C*X^2 + D*X^3 + E*X^4 In fact ...

where can I find a nurse staffing templatew for excel 2000
I need an excel template for excel 2000 that would allow me to staff nurses Try the Office Template Gallery. http://officeupdate.microsoft.com/templategallery/default.asp Gord Dibben MS Excel MVP On Mon, 17 Mar 2008 17:54:00 -0700, klif_e <klife@discussions.microsoft.com> wrote: >I need an excel template for excel 2000 that would allow me to staff nurses ...

Excel 2007 Back-up copies
How do I stop Excel from creating a "back-up" copy everytime I "save" a document? It only does this when I "Save" not when I "save as". My folders are quickly filling up with back-ups. This just started happening after I upgraded to Windows 7. -- Hawaiianshirt Does that happen for all workbooks or a specific one. Open the workbook>from Save AS dialog>Tools>General Options> check whether 'Always create backup' is enabled or not? If this post helps click Yes --------------- Jacob Skaria "Hawaiianshirt...

Time formats in Excel 2003
Being in the UK, I obviously want to use the English(UK) date format, so Christmas Day is 25/12/05 rather than 12/25/05. However, this defaults the time format to hh/mm/ss (why, I do not know, since I can assure American readers we're not normally that precise), so I have to reset it English(US) to get hh/mm, which resets the default date format to mm/dd/yy, so I have to change it back the next time I want to format a cell as a date. Any way round this? It's only a minor problem but it's irritating me. Steve Stephen, If you set up your own custom format with hh/mm, it w...

excel passwords #2
I need to know how to set up a password and change a password to prevent access to my workbook.TIA Use workbook/sheet protection. Select tools menu>protection. There you can select the type of protection you want and include a password Regards, F.I. >-----Original Message----- >I need to know how to set up a password and change a >password to prevent access to my workbook.TIA >. > ...

Excel 2003, when I click on a particular cell it gets deselected
While using Excel 2003, when I click on a particular cell, within 30 seconds, that particular cell gets deselected followed by the workbook. So each time I need to click either the cell or workbook to enter data in that particular sheet. There could be event code that is being fired. Does this happen if you open the workbook with macros disabled? HTH, Bernie MS Excel MVP "towinwin" <towinwin@discussions.microsoft.com> wrote in message news:B7B87AFB-27AC-4235-9AE3-5E7A46310AF8@microsoft.com... > While using Excel 2003, when I click on a particular cell, within 30 se...

Reading Registry Key through excel?
Is there anyway to read a certain registry key and display it in a cell how about a way to create a key through excel? is that possible -- Message posted from http://www.ExcelForum.com Rav, There is a special registry area for VBA which is read/written via GetSettinmg and SaveSetting functions. It is a bit restrictive, in that in can only writes to a limited part of the registry, but it's sufficient for storing application details. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ravz &g...

minimum cell value
Hi, I am trying to set up a cell so it has a minimum value. The current formula is: =a1/a2*5 which gives the answer 0 but i need to show a minimum value of 1 is this possible? Hi, Doug, Try: =Max(a1/a2*5,1) --- Regards, Norman "Doug Bell" <Doug Bell@discussions.microsoft.com> wrote in message news:8766BC4D-D3CD-4B4F-AF7B-FD145DE56205@microsoft.com... > Hi, > > I am trying to set up a cell so it has a minimum value. > > The current formula is: =a1/a2*5 which gives the answer 0 but i need to > show > a minimum value of 1 is this possible? > ...

Filter List for Linked Values
I have a list of Numbers. In that list some Cells are: - Linked Values [ e.g. ='[Sales.xls]Sheet1'!K61) ] - Forumlas [ e.g. =1802+254+356 or =sum(A2:B32) ] - Manually Entered I want to filter the list for linked values. Is this possible? Hi not without VBA (using a user defined function) and a helper column >-----Original Message----- >I have a list of Numbers. In that list some Cells are: > > - Linked Values [ e.g. ='[Sales.xls]Sheet1'!K61) ] > - Forumlas [ e.g. =1802+254+356 or =sum(A2:B32) ] > - Manually Entered > > I want to filter the l...

Line chart, value labeling
I like to change line graph settings so that only one value will appea on the line (not all the values). I right clicked on the point on the line, selected format data series selected Data Labels tab, checked box Show Values. and then click ok. All the data values show up on the line. But, I like to see only on specific value. How do we do that -- Message posted from http://www.ExcelForum.com Hi, You need to select the line series and then select a single point before displaying the format dialog and enabling data labels. Cheers Andy NYBoy < wrote: > I like to change line graph s...

creating a macro that will paste a value and then move
Please, help! I am trying to recreate a macro that opens a workbook, goes to Sheet 1, pastes clipboard info into A1, then moves to B1, then saves and closes the file. I know how to make the file automatic and how to get it to open and close (Auto_Open), but I cannot remember how to get the macro to move from cell to cell (left-to right). What happens is that when I do a recorded macro and then try to rerun it; it will paste into A1 and then will tab over to B1. Then upon the next time that I call up the spreadsheet, it will even paste to B1, but will not move further from that spot. I then...

Weird problem -- formulas get lost but their values stay in place
I have a client who uses Excel files that link out to other excel files. The formulas in the "main" file calculate from data in the linked files. So, the formula is in the main file. When they close the main file and re-open it, the formulas are gone, but the last calculated value remains. Does anyone have an idea why this would happen? Thanks, Bill One way might be because a "Before Save" or "Before Close" Macro is doing a Copy > Paste special > Values........ Vaya con Dios, Chuck, CABGx3 "Bill" <bdotson@gmail.com> wrote in message...

excel find problems
I have a database in excel with 4 sheets. When I want to conduct a search, it only searches the active sheet. Therefore I have to select each sepearte sheet and carry out 4 individual searches. Is there any way to search all sheets at once? If there is not, I am aware I can create a user form and use the approproate syntax to conduct my required search. However this will take me a long time. If there is an easier apprach then please let me know. Regards camron What version of excel? How are you searching? In later versions of Excel, you can select multiple sheets and have Edit|Fin...

how to copy a row of cells automatically from one worksheet to another by changing the value of1cell
Hello. I have 8 cells on the same row in one worksheet that I would like to be automatically copied to a predefined area in one of 3 other worksheets in my file depending on the number (1, 2 or 3) entered in one cell adjacent to these 8 cells. Is this possible and, if so, how could it be done? Thanks very much for any help you can offer me with this. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** David, You could use the worksheet change event: this will copy the eight cells from the s...

Excell won't open
I have produced a series of spreadsheets this morning. (Excel 2003) on my laptop. Have been saving them on a shared network folder. I have come to open them again and all I get is a the excel background with no worksheet. They all opened fine earlier this morning. Every other machine on the network can open the spreadsheets (from this location) just fine. I can open other spreadsheets from that same folder that I produced on other days. If I copy this morning's spreadsheets to my laptop, they still won't open. In all other aspects Excel works fine. If I save a new workshee...