if function
 

I need help with a formula.

I have a spread sheet that tracks equipment, replacement cost, lifespan,
replacement date and a second replacement date (xx months after it was
originally replcated)

I am have a 36 columns with each month for 3 years. I would like to look up
both replacement date cells to see if they fall in the column with the
current month/year. If it does then I would like to pull the column with the
replacement cost.

Thanks,

Tell us more about how your data is arranged.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Don" wrote in message
...
I need help with a formula.

I have a spread sheet that tracks equipment, replacement cost, lifespan,
replacement date and a second replacement date (xx months after it was
originally replcated)

I am have a 36 columns with each month for 3 years. I would like to look
up
both replacement date cells to see if they fall in the column with the
current month/year. If it does then I would like to pull the column with
the
replacement cost.

Thanks,

Hi

Better design your workbook as database. P.e.

Sheets
People: Name
Items: Item, Lifespan, Cost
Equipment: Name, Item, Date, Replaced, OutDated, Cost

On Items sheet, th Lifespan column contains p.e. the number of months the
item is to be used.

On Equipment sheet:
Column Name is formatted as data validation list with names on sheet
People as source (you have to define a named range for this - better the
dynamic one);
Column Item is formatted as data validation list with items from sheet
Items as source;
Into Date column you enter date, the item was given out;
Into Replaced column, you enter the date, when this item was replaced
with new one (you enter the new row for newly given item then too), or
scraped;
Into OutDated column the formula returns the date, calculated p.e. by
formula like
=IF(OR(C2="",ISERROR(VLOOKUP(B2,ItemsTbl,2,0))),"" ,DATE(YEAR(C2),MONTH(C2)+V
LOOKUP(B2,ItemsTbl,2,0),DAY(C2)))
(GivenOutDate+Lifespan, ItemsTbl is a named range representing the whole
table on Items sheet);
Into Cost column, the formula returns the cost of item from Items sheet,
like
=IF(OR(B2="",ISERROR(VLOOKUP(B2,ItemsTbl,3,0))),"" ,VLOOKUP(B2,ItemsTbl,3,0))
;
You can use conditional formatting on Replaced column to indicate
(colored patterns) outdated items, p.e. for cell D2 the condition formula
will be
=AND(D2="",E2<"",E2<TODAY())


Arvi Laanemets


Now you can easily design various report sheets which all get data from
Equipment sheet, p.e. the list of items in use currently (and their cost),
or the list of items, given out in some time period, or the list of items to
be replaced from now until some date, or the list of all items in use by
person (with data validation list used to select the person), etc. The only
limit is your need and your skills.
"Don" wrote in message
...
I need help with a formula.

I have a spread sheet that tracks equipment, replacement cost, lifespan,
replacement date and a second replacement date (xx months after it was
originally replcated)

I am have a 36 columns with each month for 3 years. I would like to look
up
both replacement date cells to see if they fall in the column with the
current month/year. If it does then I would like to pull the column with
the
replacement cost.

Thanks,