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!

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!

=MAX(IF(B2:B100="C4127X",D2:D100)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CatatonicBug" wrote in message
...
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!

Perfect!! Exactly what I needed! This is a lot easier than the Pivot Table
idea too!

Thanks!!

"Bob Phillips" wrote:

=MAX(IF(B2:B100="C4127X",D2:D100)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CatatonicBug" wrote in message
...
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!