Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Looking up the most recent date

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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Looking up the most recent date

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!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Looking up the most recent date

=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!



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Looking up the most recent date

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!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
count between start date and end date flow23 Excel Discussion (Misc queries) 5 May 10th 06 01:22 PM
Determining Date X based on Other dates MIchel Khennafi Excel Worksheet Functions 1 May 3rd 06 04:45 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Another Date issue. TimM Excel Worksheet Functions 1 November 17th 05 02:58 AM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM


All times are GMT +1. The time now is 12:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"