Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing values in cell based on system date | Excel Worksheet Functions | |||
On opening a worksheet move focus to a cell based on date | Excel Worksheet Functions | |||
Calculate sum of values based on a ref value, how? | Excel Discussion (Misc queries) | |||
IF statement to calculate based on cell values | Excel Worksheet Functions | |||
if cell has date value how to calculate other cell values | Excel Discussion (Misc queries) |