software

Here is what I need to happen... Below is my spreadsheet.. i am trying to
keep a report that pulls information from this spreadsheet and updates each
day with that current days numbers... So on a seperate sheet I want it to
list the current days numbers for that cell.. If the date is 01/02/06 then i
want the region 1 total errors for that day.. I was thinking something like
this.
=LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
is not working.. i need 2 criteria, date and region. Thanks

A B C
DateRegionTotal Errors
1/2/2006Region 10
Region 2683
Region 30
Region 40
Region 5376
Region 6569
Region 80
Region 9145
First, I restructure the data table to facilitate lookups, but if that's the
structure you're stuck with then this may work for you:

Assumption: You want to view error information for a single date on a
separate worksheet.

With your example data on Sheet1, beginning in Cell A1

Using Sheet2:
B1: Date
B2: 01/02/2006
C1: Region
D1: Total Errors

A2: =MATCH(B2,Sheet1!A1:A1000,0)

C2: Region 1
C3: Region 2
etc

D2: =VLOOKUP(C2,INDEX(Sheet1!$B$1:$B$1000,Sheet2!$A$2, 1):Sheet1!$C$1000,2,0)

Copy that formula down as far as you have regions listed.

Change the date in Cell B2 to another date and the formulas in Col_D will
return the relevent data for that date.

(Adjust range references to suit your situation)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Proquot;cbanksquot; wrote:

gt; Here is what I need to happen... Below is my spreadsheet.. i am trying to
gt; keep a report that pulls information from this spreadsheet and updates each
gt; day with that current days numbers... So on a seperate sheet I want it to
gt; list the current days numbers for that cell.. If the date is 01/02/06 then i
gt; want the region 1 total errors for that day.. I was thinking something like
gt; this.
gt; =LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
gt; is not working.. i need 2 criteria, date and region. Thanks
gt;
gt; A B C
gt; DateRegionTotal Errors
gt; 1/2/2006Region 10
gt; Region 2683
gt; Region 30
gt; Region 40
gt; Region 5376
gt; Region 6569
gt; Region 80
gt; Region 9145
gt;
gt;

You've got a couple of other threads going elsewhere.

cbanks wrote:
gt;
gt; Here is what I need to happen... Below is my spreadsheet.. i am trying to
gt; keep a report that pulls information from this spreadsheet and updates each
gt; day with that current days numbers... So on a seperate sheet I want it to
gt; list the current days numbers for that cell.. If the date is 01/02/06 then i
gt; want the region 1 total errors for that day.. I was thinking something like
gt; this.
gt; =LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
gt; is not working.. i need 2 criteria, date and region. Thanks
gt;
gt; A B C
gt; Date Region Total Errors
gt; 1/2/2006 Region 1 0
gt; Region 2 683
gt; Region 3 0
gt; Region 4 0
gt; Region 5 376
gt; Region 6 569
gt; Region 8 0
gt; Region 9 145

--

Dave Peterson

no not really my data needs to stay the same.. im putting together a report
and i want it to just show everyday whats happening that day.. so i need my
columns to stay the same.

quot;Ron Coderrequot; wrote:

gt; First, I restructure the data table to facilitate lookups, but if that's the
gt; structure you're stuck with then this may work for you:
gt;
gt; Assumption: You want to view error information for a single date on a
gt; separate worksheet.
gt;
gt; With your example data on Sheet1, beginning in Cell A1
gt;
gt; Using Sheet2:
gt; B1: Date
gt; B2: 01/02/2006
gt; C1: Region
gt; D1: Total Errors
gt;
gt; A2: =MATCH(B2,Sheet1!A1:A1000,0)
gt;
gt; C2: Region 1
gt; C3: Region 2
gt; etc
gt;
gt; D2: =VLOOKUP(C2,INDEX(Sheet1!$B$1:$B$1000,Sheet2!$A$2, 1):Sheet1!$C$1000,2,0)
gt;
gt; Copy that formula down as far as you have regions listed.
gt;
gt; Change the date in Cell B2 to another date and the formulas in Col_D will
gt; return the relevent data for that date.
gt;
gt; (Adjust range references to suit your situation)
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;cbanksquot; wrote:
gt;
gt; gt; Here is what I need to happen... Below is my spreadsheet.. i am trying to
gt; gt; keep a report that pulls information from this spreadsheet and updates each
gt; gt; day with that current days numbers... So on a seperate sheet I want it to
gt; gt; list the current days numbers for that cell.. If the date is 01/02/06 then i
gt; gt; want the region 1 total errors for that day.. I was thinking something like
gt; gt; this.
gt; gt; =LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
gt; gt; is not working.. i need 2 criteria, date and region. Thanks
gt; gt;
gt; gt; A B C
gt; gt; DateRegionTotal Errors
gt; gt; 1/2/2006Region 10
gt; gt; Region 2683
gt; gt; Region 30
gt; gt; Region 40
gt; gt; Region 5376
gt; gt; Region 6569
gt; gt; Region 80
gt; gt; Region 9145
gt; gt;
gt; gt;