Will a formula do what I want?

[SamanthaM]

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

 

[MrShorty]

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

 

[MrShorty]

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

 

[SamanthaM]

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

 

[Roger Govier]

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

 

[Cutter]

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

 

[SamanthaM]

When I use =VLOOKUP(A5,Price!$A$5:$F$5000,7,FALSE) I get a result o
#REF!

How can I fix that

 

[MrShorty]

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

 

[SamanthaM]

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