Calculate a date based on values in other cell, worksheet
 

I have a worksheet (Inventory) that contains colums for PartID, Category,
DateMade and DateToRecyle. I also have another sheet (RetainPeriod) in the
Workbook that has Category and RetentionPeriod. In Inventory when someone
enters the PartID, Category, and DateMade. I want to calculate the
DatetoRecycle based on the Category and its associated RetentionPeriod (in
months) from worksheet RetainPeriod. I know it's probably a VLookup but I
don't know how to formulate the function. Help is appreciated

Calculate a date based on values in other cell, worksheet
 

I presume that (at least some of) the names DateMade, Category and
RetentionPeriod are not named ranges, since I believe you cannot have two
named ranges called Category.

But suppose you had the named ranges DateMade and Category for the Inventory
worksheet and RetentionPeriod and RetentionCategory for the RetainPeriod
worksheet. Then, in the cells corresponding to DateToRecycle name range, do
something like:

=DateMade+index(RetentionPeriod,match(Category,Ret entionCategory,0))

If DateMade, Category and RetentionPeriod are simply your way of referring
abstractly to ranges like A2:A10, B2:B10 and C2:C10, then:

=A2+index(RetainPeriod!$C$2:$C$10,match(B2,RetainP eriod!$B$2:$B:$10,0))

(For simplicity, I have Category in B2:B10 on both worksheets. But of
course, they could be in different columns or rows on each worksheet.)

If and only if Category and RetentionPeriod are parallel columns (i.e. they
can be referenced with a single range) with Category on the left, as I have
assumed above, then you can replace INDEX/MATCH with VLOOKUP:

=A2+vlookup(B2,RetainPeriod!$B2:$C$10,2,0)


----- original message -----

"KS" wrote in message
...
I have a worksheet (Inventory) that contains colums for PartID, Category,
DateMade and DateToRecyle. I also have another sheet (RetainPeriod) in the
Workbook that has Category and RetentionPeriod. In Inventory when someone
enters the PartID, Category, and DateMade. I want to calculate the
DatetoRecycle based on the Category and its associated RetentionPeriod (in
months) from worksheet RetainPeriod. I know it's probably a VLookup but I
don't know how to formulate the function. Help is appreciated

Calculate a date based on values in other cell, worksheet
 

In your DateToRecycle column enter the formula:
=EDATE(C2,VLOOKUP(B2,RetentionPeriod!A1:B7,2,FALSE ))
where C2 is the DateMade
B2 is the Category
RetentionPeriod is the sheet containing the table of Category and
Months

The range of the Retention Table will need to be adjust depending on the
number of Categories.

"KS" wrote:

I have a worksheet (Inventory) that contains colums for PartID, Category,
DateMade and DateToRecyle. I also have another sheet (RetainPeriod) in the
Workbook that has Category and RetentionPeriod. In Inventory when someone
enters the PartID, Category, and DateMade. I want to calculate the
DatetoRecycle based on the Category and its associated RetentionPeriod (in
months) from worksheet RetainPeriod. I know it's probably a VLookup but I
don't know how to formulate the function. Help is appreciated

Calculate a date based on values in other cell, worksheet
 

Thanks Joe..
For simplicity sake, I tried the =A2+vlookup(B2,RetainPeriod!$B2:$C$10,2,0)
function and it works to an extent. In the lookup table I want to indicate
number of months, the formula adds number of days. I'm sure for you that's an
easy fix, appreciate the help. -KS

"JoeU2004" wrote:

I presume that (at least some of) the names DateMade, Category and
RetentionPeriod are not named ranges, since I believe you cannot have two
named ranges called Category.

But suppose you had the named ranges DateMade and Category for the Inventory
worksheet and RetentionPeriod and RetentionCategory for the RetainPeriod
worksheet. Then, in the cells corresponding to DateToRecycle name range, do
something like:

=DateMade+index(RetentionPeriod,match(Category,Ret entionCategory,0))

If DateMade, Category and RetentionPeriod are simply your way of referring
abstractly to ranges like A2:A10, B2:B10 and C2:C10, then:

=A2+index(RetainPeriod!$C$2:$C$10,match(B2,RetainP eriod!$B$2:$B:$10,0))

(For simplicity, I have Category in B2:B10 on both worksheets. But of
course, they could be in different columns or rows on each worksheet.)

If and only if Category and RetentionPeriod are parallel columns (i.e. they
can be referenced with a single range) with Category on the left, as I have
assumed above, then you can replace INDEX/MATCH with VLOOKUP:

=A2+vlookup(B2,RetainPeriod!$B2:$C$10,2,0)


----- original message -----

"KS" wrote in message
...
I have a worksheet (Inventory) that contains colums for PartID, Category,
DateMade and DateToRecyle. I also have another sheet (RetainPeriod) in the
Workbook that has Category and RetentionPeriod. In Inventory when someone
enters the PartID, Category, and DateMade. I want to calculate the
DatetoRecycle based on the Category and its associated RetentionPeriod (in
months) from worksheet RetainPeriod. I know it's probably a VLookup but I
don't know how to formulate the function. Help is appreciated

Calculate a date based on values in other cell, worksheet
 

Thanks Vince,
The formula works great, I just had to change the RetentionPeriod reference
to an absolute reference to copy the formula down the column. -KS

"Vince" wrote:

In your DateToRecycle column enter the formula:
=EDATE(C2,VLOOKUP(B2,RetentionPeriod!A1:B7,2,FALSE ))
where C2 is the DateMade
B2 is the Category
RetentionPeriod is the sheet containing the table of Category and
Months

The range of the Retention Table will need to be adjust depending on the
number of Categories.

"KS" wrote:

I have a worksheet (Inventory) that contains colums for PartID, Category,
DateMade and DateToRecyle. I also have another sheet (RetainPeriod) in the
Workbook that has Category and RetentionPeriod. In Inventory when someone
enters the PartID, Category, and DateMade. I want to calculate the
DatetoRecycle based on the Category and its associated RetentionPeriod (in
months) from worksheet RetainPeriod. I know it's probably a VLookup but I
don't know how to formulate the function. Help is appreciated