How do you determine the revenue for the machine? Is it the value in the
REVENUE worksheet for the last year the machine was in use? Or is it the
value where the year is 2004? If you could structure your REVENUE data (or
create a separate table) so that it only contains one row per machine, then
you could use a VLOOKUP formula in the DATE worksheet:

=vlookup(MachineLocation, RangeWhereRevenueIsHeld,
ColumnContainingTheRevenue, FALSE)

Check the Help for more info on VLOOKUP. Post back if this is unclear.

--
Dianne Butterworth

Hi,

Need some urgent help. Here is what I'm trying to do:

I have a number of machines at different locations. There is a list of
dates which specify the end of life for a machine. When that life ends
we need to replace the machine or buy a new machine. We can replace it
by another machine from our fleet if there is a machine whose revenue
is less than 30.

So we go through the date list one by one (in the sorted order so that
we attend to the machine which has the earliest end date), find a
replacement for it (which has revenue less than 30- if it doesnot have
any replacement we say buy a new one). And we can display it any
format on a new sheet.

In my excel workbook I have 2 worksheets. In the DATE worksheet I have
2 columns, one with machine locations and one with end of life dates.
They are sorted in ascending order of end of life. In REVENUE
worksheet, I have information about revenue earned by each location
for every year for a period of 15 years.

I've been trying to do this for long and need to get it done asap.
Please help. I can even send you the workbook if you email me at


I really appreciate the time you have taken to read this post :)
Thanks a lot

Kerl


---
Message posted from http://www.ExcelForum.com/