RE: Looking up the most recent date
- From: Ron Coderre <ronREMOVETHIScoderre@xxxxxxxxxxx>
- Date: Fri, 8 Sep 2006 10:36:01 -0700
I suspect a Pivot Table would be the easiest-to-maintain solution.
Using your data table....
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button
ROW:
Drag the "Machine#" field here
Double-click the field and set the subtotal to None
Drag the "Toner" field here
Double-click the field and set the subtotal to None
Drag the "Replaced" field here
Double-click the field and set the subtotal to None
DATA:
Drag the "Installed" field here
Double-click the field and set the function to MAX
Click [OK]
Select where you want the Pivot Table...and you're most of the way there.
Click the "Replaced" field dropdown on the Pivot Table and only check: Blank
That will leave you with a table of Machine#'s, toner types, and the last
install date that hasn't yet been replaced.
If you prefer, you can drag the Machine# field to the PAGE area so you can
view one machine at a time.
To refresh the Pivot Table, just right click it and select Refresh Data
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"CatatonicBug" wrote:
I have a Database in which I keep track of all the toner for my company. I.
list each box as it arrives with a number, and record it (1,2,3...345,346,
etc.). When it arrives, I record the date in one column, when I install it in
a machine, I record that date in the next column, and when I replace it with
a new one, I record that date in the next column. It looks something like
this:
A B C D E
F G
1 Ref # Toner Arrived Installed Machine# Replaced
Toner Life
2 1 C4127X 1/26/05 2/17/05 10185268 3/14/05
25
I have 62 different machines, and 50 types of toner and other supplies. I
maintain a fairly good JIT ordering system for this stuff, but i want to make
it one step better.
What I want to be able to do is display on a list of all the different
machines (several machines use the same type of toner) the most recent date
of installation for each machine, then use that number to determine how many
days are left on that installation, based on the average number of days in
the "Toner Life" column. I have already figured the average, but I need to be
able to pull out the date of the most recent installation for each machine,
and plug that into a formula. How do I retrieve that info for each machine?
I know this sounds crazy, but any help would be appreciated.
Thanks!
- Prev by Date: Re: Syntax for a relative formula
- Next by Date: Re: Calculating Age
- Previous by thread: Syntax for a relative formula
- Next by thread: Re: Looking up the most recent date
- Index(es):
Relevant Pages
|
