Will a formula do what I want?

Using: Excel 2000, on Windows 98 

I'm hoping that someone on here can possibly help me. I have basi
Excel knowledge, but this is out of my skill set. 

My boss has given me a project to work on, in which I have a workboo
with two worksheets that I am dealing with. The worksheets are labelle
PRICE and SUMMARY. 

On the PRICE sheet, I have several columns. Column A lists the produc
name, Columns E list the per page charge for the *red* program, an
Column G lists the per page charge for the *blue* program. Those ar
the columns we will be dealing with. All specific information on thi
page starts on Row 5, with the headings, etc on Rows 1 - 4.

On the SUMMARY sheet, we have Column A which lists the product name
Column E which lists the cost per page for *red* and Column G whic
lists the cost per page for *blue*. As with the PRICE sheet, al
specific information on this page starts on Row 5, with the headings
etc on Rows 1 - 4.

What I need to be able to do, is when one of our sales guys enters 
product name in Column A on the SUMMARY worksheet (I have this set u
with Data Validation, so the name will be the same format on both th
PRICE and SUMMARY worksheets), have columns E & G automatically fill i
with the corresponding information (columns E & G) from the PRIC
worksheet, without the sales guy having to go and look up th
information on the other sheet. 

I'm not very experienced with setting up formulas (but I'm trying t
learn!), so any help and guidance you could offer would be greatl
appreciated. 

Samanth

--
Samantha
-----------------------------------------------------------------------
SamanthaM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2783
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 7:16:32 PM
excel 39879 articles. 2 followers. Follow

11 Replies
640 Views

Similar Articles

[PageSpeed] 25

Have you tried VLOOKUP?


-- 
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22181
View this thread: http://www.excelforum.com/showthread.php?threadid=473544

0
10/5/2005 7:33:46 PM
MrShorty Wrote:
> Have you tried VLOOKUP?

I'm not entirely sure how to go about setting it up to use VLOOKUP
I've read the info provided in Excel Help, but I'm lost

--
Samantha
-----------------------------------------------------------------------
SamanthaM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2783
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 7:36:17 PM
See if this helps further (hopefully I get the references right, if no
adjust as necessary):

In E5: =VLOOKUP($A5,price!$A$5:$G$5000,5,true)
copy down E as far as needed, copy to column G and replace the 5 in th
third argument to 7 and copy down column G

Does that help

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 7:54:05 PM
It does help... It actually did something other than give me an erro
message!!! Unfortunatly, it gives a result of 0, instead of $.0120 lik
it should for the item I'm using as my test.

Just making sure... WHen you say "third argument", do you mean that 
need to change the 5 that appears here (the one that I bolded)
=VLOOKUP(A5,price!$A$*5*:$F$5000,5,true)  ???

Thank you so much for the help so far! The sheet finally did somethin
other than give me an error

--
Samantha
-----------------------------------------------------------------------
SamanthaM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2783
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 8:00:48 PM
Yes that's the third argument.

And, one other mistake I made: change the true to false for the 4t
argument (I always get that toggle confused).  True requires that you
"Price" data be sorted and it doesn't look for an exact match.  You ma
not be able to sort your data, and with data validation can reasonabl
expect to be searching for exact matches, so set it to false

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 8:06:41 PM
So my code should be as follows then?

For RED    =VLOOKUP(A5,Price!$A$5:$F$5000,5,FALSE)
This gives me the right result.


For BLUE  =VLOOKUP(A5,Price!$A$7:$F$5000,5,FALSE)
This doesn't. It gives the same result as the one for RED, when th
answer should be something different.  Am I missing something

--
Samantha
-----------------------------------------------------------------------
SamanthaM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2783
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 8:18:19 PM
Hi Samantha

No,Mr Shorty meant the ,5,true should be ,7,true for column G.

The True part of the formula, assumes that your products are sorted 
alphabetically.
Try changing the true to False and see if that gives you the correct result.

Regards

Roger Govier



SamanthaM wrote:

>It does help... It actually did something other than give me an error
>message!!! Unfortunatly, it gives a result of 0, instead of $.0120 like
>it should for the item I'm using as my test.
>
>Just making sure... WHen you say "third argument", do you mean that I
>need to change the 5 that appears here (the one that I bolded):
>=VLOOKUP(A5,price!$A$*5*:$F$5000,5,true)  ???
>
>Thank you so much for the help so far! The sheet finally did something
>other than give me an error!
>
>
>  
>
0
roger1272 (620)
10/5/2005 8:36:03 PM
Sorry to jump in but I thought I'd clear up a mix-up you 2 had.

Your second formula should be:

=VLOOKUP(A5,Price!$A$5:$F$5000,7,FALSE)

You changed the 2nd argument, not the third

--
Cutte
-----------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 8:42:48 PM
When I use   =VLOOKUP(A5,Price!$A$5:$F$5000,7,FALSE)  I get a result o
#REF! 

How can I fix that

--
Samantha
-----------------------------------------------------------------------
SamanthaM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2783
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 8:44:56 PM
The third argument tells the function which column to pull the resul
from.  The reference as you have it is A:F which is only 6 column
wide.  Thus, you get an error when trying to extract something from th
7th column.  I tried to correct my original post to include column G
but I guess I was too slow

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 8:48:51 PM
You weren't too slow! 

You both saved the day!!! I needed to correct the argument that Cutte
mentioned, and I also needed to change the F to a G that you mentioned
and now it works! 

Thank you, thank you, thank you! I can stop pulling my hair out now

--
Samantha
-----------------------------------------------------------------------
SamanthaM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2783
View this thread: http://www.excelforum.com/showthread.php?threadid=47354

0
10/5/2005 8:53:46 PM
Reply:

Similar Artilces:

WMP 11 will not "add to library"
I was out one day when the power went out. My computer obviously shut down, and as a result WMP shut down improperly. This hasn't been the first time, and whenever it does, I just start up my computer again, log in, and WMP 11 is working just fine. There has also been some instances when the library has been corrupted, and I've just re-added my files to the library again. However, this time it's different apparently; WMP 11 refuses to add ANYTHING to it's library, and whenever I choose a folder for it to monitor, it does absolutely nothing, as opposed to adding ...

Formula Help #54
What would be a good formula to use in this situation? If you have in column A: 50010 Apples 50020 Bananas Subtotal 50110 Carrots 50120 Lettuce Subtotal In column B I have numbers 6 5 11 4 5 9 If I then get revised numbers without the description, lets say in column C and D respectively: 50010 10 50020 20 50030 30 50040 40 How can I create a formula in column B that takes the revised 50010 figure and moves it into column B replacing the old figure of 6 with 10 without doing it manually because I am looking at a much larger scale. Thanks for your help. -- billy2wil...

Revised formula question
I guess I haven't explained my first questions very well. The answer will vary depending on what amount the formula is being calculated against. The answer will not always be same. I need to have a formula that calculates commission on the first $1500 and not any additional money on anything over $1500. So if a person raises $1300. The percentage is .034 - the commission would be $44.20. If a person raises 1500. the percentage is still .034 the commission would be $51.00. If the person raises 1600, the percentage is .034 for the first $1500 and the commission is still $51.00 because he...

Pivot If functions in formulas
I am trying to use the If function in the formulas area and it never works right, I only want it to sum certain records in a particular date range. I am using excell 2000. ...

External users will use only smtp/pop3 connection to Exchange Serv
External users will use only smtp/pop3 connection to Exchange Server. I dont won't create AD users with mailboxes. Are there any other ways to resolve this problem? On Thu, 8 Dec 2005 22:44:01 -0800, "Slava" <Slava@discussions.microsoft.com> wrote: >External users will use only smtp/pop3 connection to Exchange Server. Fine, that's perfectly OK. > >I dont won't create AD users with mailboxes. Oh yes you will. No AD account = No Mailbox. No Mailbox = No access to the server. > >Are there any other ways to resolve this problem? What problem? ...

What does the $ symbol mean in a formula?
What does the $ symbol mean in a formula? example: =G8*($A$14*2)*100 thanx The $ is used for absolut referencing. For e.g lets take your formula =G8*($A$14*2)*100 suppose you enter this formula in cell B2, now simply copy this formula one cell to the right (C2) by and one cell down (B3). C2: =H8*($A$14*2)*100 B3: =G9*($A$14*2)*100 So you see that The original G8 has changed, whereas $A$1 remains the same in both the new formulae because of the dollar signs. Mangesh "S" <S@discussions.microsoft.com> wrote in message news:D86597A4-C0A1-46AF-9DA6-FE06AB9C1FA0@mi...

Determine if another cell is hidden in a Formula
Hi! I would like to display a special message in a cell when another cell is hidden. Is it possible? Something like (in cell A2): =If(IsHidden(A1), A1, "") I guess I could do it with a user function... but if I use that in a lot of cells (hundreds) it might significantly slow down the refresh speed... Any thoughts ? J Whales ...

Conditional formula 05-12-10
trying to construct a formula for the following for the same cell: if a2 > 6, then e2 = 0 if a2 = 6, then e2 = 1 if a2 = 5, then e2 = 2 if a2 = 4, then e2 = 3 if a2 = 3, then e2 =5 this is for a golf scoring system. a2 values are hole scores and results are "handicap" point scores. Any help greatly appreciated Try this: =3DIF(A2>6,0,IF(A2<=3D3,5,7-A2)) Hope this helps. Pete On May 12, 4:14=A0pm, desmond1412 <desmond1...@discussions.microsoft.com> wrote: > trying to construct a formula for the following for the same cell: > if a2 > ...

Does anyone have a formula to calculate the car lease payments
Hi, I am just looking for a formula that will allow me to calculate the monthly repayments for a car lease. Thanks in advance The PMT function may help you out. Look at Excels help for details regarding its arguments and use. "Captain Jack" wrote: > Hi, > > I am just looking for a formula that will allow me to calculate the monthly > repayments for a car lease. Thanks in advance see if this helps http://tinyurl.com/dxnq4 -- Don Guillett SalesAid Software donaldb@281.com "Captain Jack" <Captain Jack@discussions.microsoft.com> wrote in message ...

percent formula
Anyone please! How do I write a formula that gives me the total after deducting %. Example if I have $25 and I need to deduct 14% and I need to show the total which would be 21.50 -- smile Try this: =25*(1-14%) A1 = 25 B1 = 14% =A1*(1-B1) Format as General or Number -- Biff Microsoft Excel MVP "israel" <israel@discussions.microsoft.com> wrote in message news:E5C67430-5992-46A1-B9EC-40C0DC443CDA@microsoft.com... > Anyone please! > > How do I write a formula that gives me the total after deducting %. > Example if I have $25 and I need to deduct 14% and I...

excel formula problem #3
i want to set up a system that counts a series of dates but only say how many cells are filled, i can get this to work generally but section needs to count 3 seperate dates but only add on any one of the (eg) cell A counts the number of dates in cells B-Z but with cells D,E, i dont want all to count, only one of the three even if all are filled but it must count if any of the three are filled hope this makes sens -- Message posted from http://www.ExcelForum.com Hi try: =COUNT(B1:C1,G1:Z1)+(COUNT(D1:F1)>0) -- Regards Frank Kabel Frankfurt, Germany > i want to set up a system that c...

Strange behavior when copying formula.
Hi, Here is what I'm trying to do. I want to show the summation of the first two rows' number in the third row. I use the summation function to get the first result. Then, I copy and paste the result to the other two, but they show the same results as the first one. I check the formula of all three results, and they are all correct and referring to the right cells. Why is that? 1 2 3 1 2 3 2 2 2 Leo Do you have Tools/Options/Calculation set to Manual? If so, click the Automatic button. In article <eE#EeXFhDHA.2512@TK2MSFTNGP09.phx.gbl>, "Leo Leon...

Sum formula is not adding up properly
I am summing up hours in Excel and the sum formula is not working Properly. For 2 of my 5 cells are adding correctly, but the other 2 when added to the formula throw the entire thing off. They are all formatted the same in 13:33 format to measure the # of hours spent on an activity. What would the reason be that two of them are not working? (it is almost like exel is substracting hours when these 2 cells are added) No real problem. Your math is probably fine; formatting needs fixing. Select the cell with the sum in it. Format this cell as Time 37:30:55 -- Gary's Student &quo...

Transposing Excel Formula
I have the following formula to calculate the probability of an option trading below price B3 Prob = =NORMSDIST((LN(B3/B2))/((B4/100)*SQRT(B5/365))) B3 = Target Price B2 = Stock Price B4 = Implied Volatility B5 = Days to Expiration I want to transpose the formula to solve for B3, assuming a value for Prob. Here's what I got, but it doesn't give the expected answer - did I make a mistake in these unfamiliar functions?: B3= EXP(NORMSINV(Prob))*((B4/100)*SQRT(B5/365))*B2 On Feb 7, 10:14=A0am, Steveal <steve_alm...@yahoo.com> wrote: > I have the following formula to calculat...

Problem with a Array formula, Please help !!
What is the problem with the array formula (see cells in column R, which are highlighted coloured yellow) ? {=IF(SUM(IF(ISERROR(LOOKUP(N(OFFSET($B2,,{0,2,4,6,8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210))),0,IF(LOOKUP(N(OFFSET($B2,,{0,2,4,6,8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210))="Y",1,0)),IF(ISERROR(LOOKUP(T(OFFSET($B2,,{0,2,4,6,8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210))),0,IF(LOOKUP(T(OFFSET($B2,,{0,2,4,6,8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210))="Y",1,0))) >0,"Y...

formula to project revenue
Hello, is there a formula I can use that will project revenue in real time based on total sales at any given time. Basically, take my sales to date, consider how many days into a calendar year we are at presently and make a prediction to where the sales will be at the end of the calendar year based on sales to date. So, say I am 50 days into the calendar year and my sales are 500,000. Presently, I am averaging 10,000 per day and at this rate my sales for the year will be 3,650,000. Is there a formula that will take the revenue from cell1 and provide the annual projected reven...

formula #60
Dear all, When I enter "=A2" for example into a sheet it stayes as =A2 instead of bringing the data. Can anyone explain this please? XP Pro SP2 Excel 2003 John North Yorkshire UK My guess is that the cell is formatted as text. Go to Format, Cells, Number. Under Category click General and then click OK. Then retype =A2. Hopefully this will fix it. "John Proud" wrote: > Dear all, > > When I enter "=A2" for example into a sheet it stayes as =A2 instead of > bringing the data. > > Can anyone explain this please? > > XP Pro SP2...

Custom addition formula
Using Office2000 Sp3 I use Excel a lot for historical things including eg; areas of land expressed in ACRES ROODS PERCHES which is not added in tens (similar to �/s/d). 40 perches = 1 Rood, 4 Roods = 1 Acre, 160 perches=1 acre. I have a formula which when applied to entries across 3 columns gives a decimal answer. The formula is =SUM(((D1*160))+(E1*40)+F1)/160 assuming acres are in D1, Roods E1, perches F1. Once this is decimalised I can work out ratios etc. However, I occasionally get tabbed text files or excel files from people who have entered lots of raw data as eg ARP where it would be ...

Can someone write me a programme, I will pay!
I want a Invoice Statement system for Horses that have percentage for each owner. I can email my excel billing to change to Access Thanks in advance.........Bob Vance Bit woolly on the details Bob. What exactly do you need help with (don't say the whole thing, you need RentACoder for that)? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob" <gallop87@hotmail.com> wrote in message news:cfco39$87o$1@lust.ihug.co.nz... > > > I want a Invoice Statement system for Hor...

Show required fields will not stay on
Has anyone else seen this situation or know how to correct it? We are using GP dynamics 7.5 on MSSQL. I can't seem to find a rhyme or reason as to what is changing this. Sometimes when I start out for the day, show required fields is on/marked, sometimes it is not. Sometimes when I change companies in dynamics, it turns off. Any help would be greatly appreciated in how to turn this on for a user and leave it on. ...

averages/if formulas-need help ASAP!!
I have two columns: one has initials of person completing the task, next to it is the percentage of work that is equal to. At the bottom, I am hoping to have two tables depicting the average % of work and total % of work for each person. I've tried =average(e3:e23 (if(d3:23="dj")) Basically I have no idea how to create a correspondence between the name and % of work...HELP! I'm so confused I can't even really explain my problem :) >I've tried =average(e3:e23 (if(d3:23="dj")) That's pretty close! Try it like this... Array ente...

Formula for cycle time
Hi, I need a formula to determine cycle time... actually to calculate the number of days from the date in one cell to the date in the other and put it in a third cell... thank you! JBell XL stores times as integer offsets from a base date, so you can use regular math functions: =A2 - A1 In article <1bf6e01c45232$5564ac40$a601280a@phx.gbl>, "JBell" <anonymous@discussions.microsoft.com> wrote: > Hi, > I need a formula to determine cycle time... actually to > calculate the number of days from the date in one cell to > the date in the other and p...

I want to format a number cell without the decimal and without ro.
I need to format the number cell so that it will still show the numbers after the decimal, but not show the decimal point at all. It must also display leading zeros, which is not a problem. I just need a way to strip the decimal from the number. If if needs to stay a number with the same value, I don't think you can do it. If you can change the value Just multiple the number by ten to the number of decimal places. If it can be text set up a new column and use =substitute(a1,".',"") "LAM" wrote: > I need to format the number cell so that it will sti...

Different formatting in a cell with a concatenated formula? #2
Yes, if a1 = blue, and b1 = red, and the formula in cell c1 is =a1&b1, want the result to look like this: bluere -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27440 Formulas don't support this character by character formatting. If you can convert the results of the formula to values, you can do it though--but that destroys the formula. andy2812 wrote: > > Yes, if a1 = blue, and...

Add rows to a formula when copying
I have the following formula in cell C3 =IF((Calculations!C9-Calculations!C15)=0," n/a",Calculations!C3/(Calculations!C9-Calculations!C15)) I wish to copy this to cell C16 but wish to add more than the 13 row (ie 16 - 3) to each reference in the worksheet Calculations. I need i to add 27 rows. The end result formula I need in cell C16 is =IF((Calculations!C36-Calculations!C42)=0," n/a",Calculations!C30/(Calculations!C27-Calculations!C42)) Is there any easy way to do this? Happy to do it using a macro i necessary. To sum up, I would like to copy a formula and...