Hello.
Here is my problem. I am pretty good at Excel but I am stuck on where
to start. I have a workbook that has multiple sheets. Each one of
these sheets is broken down as follows;
Tape Drives ? Product Sheet
Disk Systems ? Product Sheet
Software Products - Product Sheet
July Product Sales Reports
Each of the product sheets has a part number, description and a point
value for each $5000 threshold for the product sold, e.g. if a disk is
sold for $10,000 then it would be worth 2 points. If the sale were
$5000 it would be worth 1 point.
I have to take the July Sales Reports, and match up each product sold,
it?s dollar value of the sale, and match it to the product sheets and
get a final point tally.
For example:
If I sold a
disk = $10,000, this would be worth 2 points
a tape drive = $5000, this would be worth 1 point
and a software product = $15,000, this would be worth 3 points.
Easy to do if I only have three sales, but if I have hundreds of sales
then it could get nasty. I would like to automate this as much as
possible. Is this better to dump into an Access database, or keep in
Excel and use VLOOKUP.
Please help.
Greg |
Request for Question Clarification by
palitoy-ga
on
01 Sep 2005 14:31 PDT
Hello Greg
I am a little confused at what you want as an answer here... from your
question above it appears that you get one point for every $5,000 of
product value.
Do you need to copy the point value for each line in each product
sheet onto a summary sheet using something like is indicated on this
page:
http://216.109.124.98/search/cache?p=excel+copy+from+other+worksheets&ie=UTF-8&rls=org.mozilla%3Aen-GB%3Aofficial&ei=UTF-8&pstart=1&b=11&u=www.maths.tcd.ie/%7Enora/FT351-1/Excel_notes_2.pdf&w=excel+copy+other+worksheets&d=E500CD74D6&icp=1&.intl=uk
Any further clarifications would help immensely with being able to
provide you with further help. Also would it be possible to place a
copy of the Excel spreadsheet you are using online somewhere?
|
Clarification of Question by
googlygas2005-ga
on
01 Sep 2005 18:09 PDT
Definitely.
What I am trying to do is match the part number in the July Sales
Sheet to the part number in the respective product sheet. In each
product sheet on the same row is the point value for that item per
$10,000 (I was incorrect , it?s one point per $10,000).
So if I sold a disk array in the July Sales Sheet for $20,000. Then
it would correlate with the disk worksheet part number and assign how
many points per $10,000 that the sales were for.
I can email and also put on a FTP site for you possibly.
|
Request for Question Clarification by
palitoy-ga
on
02 Sep 2005 01:06 PDT
Unfortunately we are not allowed to use email here at Google Answers
so the only way we could see your spreadsheet is if it is placed
online somewhere.
As you suggested I would definitely be looking to utilise the VLookUp
function. There are a couple of excellent tutorials here:
http://www.personal-computer-tutor.com/vlookup.htm (simple version)
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=446 (more advanced)
http://www.techonthenet.com/excel/formulas/vlookup.php
The final link above has what I think is a similar example to what you
require. Your formula would be something similar to:
=VLookup(CellReferenceForPartNumber,
WorksheetNameWhereProductNamesAre!A1:Z99999,
ColumnNumberWhereTheNameOrValueRequiredIs, FALSE)
Do you require any further assistance on how to use this function or
is this sufficient for an answer?
|
Clarification of Question by
googlygas2005-ga
on
02 Sep 2005 06:09 PDT
Thank you for all the help so far.
Here is the link to the spreadsheet;
http://franklinsolt.50megs.com/Balanced%20Performance%20Product%20point%20Matrix%20June%202005.xls
Please let me know if this helps.
|
Request for Question Clarification by
palitoy-ga
on
02 Sep 2005 07:07 PDT
I think this clarifies things a little... how is the July Sales sheet
made? Do you want to type in the part number and then it
automatically do the rest or do you require the July Sales sheet to
have every item on it (whether a sale is made or not)?
|
Clarification of Question by
googlygas2005-ga
on
02 Sep 2005 07:43 PDT
The July Sales Sheet is derived from an internal Access database that
dumps what you see into an Excel sheet.
Typing in the part number on the July Sales Sheet and getting the
point value would be great. E.g. I would type a pert number the was
sold in July into an input box, it would calculate the points for the
whole month sold for that part, if 10 of an item at $10,000 per sale
the it would automatically calculate 10 points from a total of
$100,000 worth of sales of that item.
Does that help?
|
Clarification of Question by
googlygas2005-ga
on
06 Sep 2005 11:19 PDT
Should I go with the last suggestion then?
|
Clarification of Question by
googlygas2005-ga
on
06 Sep 2005 11:32 PDT
I used:
=VLookup(CellReferenceForPartNumber,WorksheetNameWhereProductNamesAre!A1:Z99999,ColumnNumberWhereTheNameOrValueRequiredIs,
FALSE)
into this:
=VLOOKUP(B6:B157,'Tape Libraries'!A1:Z99999,B6:B157, FALSE) on the
July Sales Sheet and I get = #VALUE!
Thanks,
Greg
|
Request for Question Clarification by
palitoy-ga
on
07 Sep 2005 05:27 PDT
Hello Greg
Sorry for not getting back to you sooner, I am preparing to get away
on vacation and this question slipped off the bottom of my radar.
The error #VALUE normally means that the spreadsheet cannot locate something.
The function should be this:
=VLOOKUP(B156,'Tape Libraries'!$A$1:$Z$9999,2,FALSE)
When you type this into a cell you can change the B156 cell reference
to whatever cell you wish to work on. As the other cell references
are locked (by using the $ character) it will always search the whole
'Tape Libraries' worksheet and you can paste the formula to other
lines and the first value in the VLookUp function will increment by
one.
The "2" value indicates that you want the value in the second column
returned (in this case the value in the Description would be returned,
if you wanted the List Price you would change the 2 for 3).
In summary, the vlookup function is taking the value in B156 and
looking for it in the Tape Libraries worksheet anywhere in
rows/columns A1 to Z9999. When it finds it it prints out the value in
the second column of the Tape Libraries worksheet (the Description of
the part number in B156).
I hope this helps, I am still a little confused with this but
hopefully we can work together to achieve the result you need.
|
Clarification of Question by
googlygas2005-ga
on
07 Sep 2005 14:19 PDT
Thanks. I think that we are getting closer.
I am getting #N/A now.
|
Request for Question Clarification by
palitoy-ga
on
08 Sep 2005 01:09 PDT
Excel returns #N/A when the value in the VLookUp is not found. Is
this what you would have expected when searching for the part number?
Was the part number on the worksheet you were searching?
To get a more friendly error you could use something like this:
=IF(COUNTIF('Tape Libraries'!$A$1:$Z$9999,B156)>0,VLOOKUP(B156,'Tape
Libraries'!$A$1:$Z$9999,2,FALSE),"no match found")
This counts the number of times B156 is found in the worksheet 'Tape
Libraries' and if the count is more than 0 (that is the value in B156
is found) then it performs the VLookUp. If the value in B156 is not
found then it puts "no match found" in the cell, you can of course put
whatever you wish here or even just "".
|
Clarification of Question by
googlygas2005-ga
on
08 Sep 2005 14:09 PDT
I got =IF(COUNTIF(Master!$A$2:$Z$9987,A3)>0,VLOOKUP(A3,Master!$A$2:$Z$9987,4,FALSE),"no
match found") to work and get no match found on everything. And I do
not get it. I have the same part number in Master that I have in the
referencing spreadsheet.
|
Request for Question Clarification by
palitoy-ga
on
09 Sep 2005 01:32 PDT
I do not have a "Master" worksheet on the spreadsheet link that you
sent to me. Is this correct? On the spreadsheet I have here the
formulae appear to work correctly.
Unfortunately I will not be able to spend any more time on this
question as I will be away until 5 October. Hopefully another
Reseacher will see this question and pick it up.
|
Clarification of Question by
googlygas2005-ga
on
11 Sep 2005 16:26 PDT
I finally got it to work! Thanks for all the help.
|
Request for Question Clarification by
palitoy-ga
on
12 Sep 2005 00:17 PDT
Out of curiosity how did you solve this in the end? Should I post my
work as an answer for you to close this question?
|
Clarification of Question by
googlygas2005-ga
on
12 Sep 2005 11:34 PDT
Welcome back. Hope your holiday went well.
I just used the formula that you posted and then took each point value
for each sales in July and multiplied the point value by a number and
go the final answer.
Yeah, I would like to see your work.
Thank you very much for the help!
|
