Calculate a date based on values in other cell, worksheet

[KS]

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

 

[JoeU2004]

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,RetentionCategory,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,RetainPeriod!$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 -----

 

[JoeU2004]

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,RetentionCategory,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,RetainPeriod!$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 -----

 

[Vince]

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.

 

[Vince]

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]

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

 

[KS]

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

 

[KS]

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

 

[KS]

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