vlookup newbie quest.

[a911er]

Hi I have a spreadsheet that is used to keep track of parts taken by
sales reps. It has dates on the left column and approx 80 other
columns corresponding to the individual parts. each parts column can
have a vlue of 0 , 1 or more depending on how many parts they have
taken on that day. What I am trying to do is (on another sheet) is
enter a certain date and have it return a chart of parts taken that
day(zero parts coumns excluded). this is for the purpose of giving the
reps a sheet detailing just the parts they took that day. Is vloookup
the best way, and HOW or is there a way to make this update happen
automatic on the second sheet.
thanks for the help

 

[Frank Kabel]

Hi

just to give you some ideas as you asked a lot of questions
1.A. You can use VLOOKUP on the second sheet to get your data for one
specific date. Lets assume your specific date is entered in A1 on sheet
2; B1 to XX1 duplicate the heading row for your parts. In sheet 1 you
use column A for the dates and row 1 as heading for your parts. On
sheet 2 / cell B2 enter the following
=VLOOKUP($A$1,'Sheet 1'!$A$AZ$999,COLUMN(),FALSE)
copy this for the entire row
Now you have duplicated your specific row 8including zeros) on sheet 2

1.B. Alternatively if you know the starting date on sheet 1 and the
dates are sorted in ascending order you can also calculate the
difference between your selected date and the starting date and use
OFFSET to reference the specific row of data

2. To chart only the non zeros have a look at the following site:
http://andypope.info/charts/piezeros.htm
Andy Pope explains how you can - with the use of OFFSET and SMALL as
well as named ranges for charts - eliminate zeors in a pie chart. This
technique can be applied to other charting types as well

HTH
Frank

 

[Peter Atherton]

Hi

I would not go for a Lookup on this. Instead I'd try a
Filter. You would need to have a column for the sales
persons id or name in the list of parts.

In Excel 2002 you can use an advanced filter to copy to
another sheet. On earlier versions a macro will do the job.

Even on other versions you can copy the data to another
part of the spreadsheet.

Try the on-line help and also Debra Dalgliesh's site
contextures.com for an excelent tutorial on autofilters
and advanced filters including a macro by Tom Ogilvy to
copy data to another sheet.

Regards
Peter