Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nest functions in CELL function
I have two formulas that I wish to nest.
This formula automatically fixes the format of time if it is in the wrong format when exported from another system: IF(CELL("format",B2)="D9",B2/60,IF((LEN(B2)-LEN(SUBSTITUTE(B2,":","")))=1,--("0"&B2)/60,--("0"&B2))) eg: it will fix: :45 to 00:00:45 01:05 to 00:01:05 :12:34 to 00:12:34 Now where B2 is the value of a statistic which in this case is from an employee's time for something. I can use Vlookup to separately bring in the correct times from another workbook: VLOOKUP($A2,[All.xls]day1!$A$2:$M$105,2,FALSE) where A2 is the name of the employee in a manager's workbook and [All.xls]!$A$2:$M$105 is the employee's name and statistics. The problem is I need to combine these 2 functions together. So i need some way of returning the cell address B2 in the first function and when I have tried the address and match function it has not worked. eg. ADDRESS(MATCH(A2,[All.xls]day1!$A$1:$A$105,0),2) even though this formula on its own correctly returns B2 the nesting does NOT work and throws an error. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nest functions in CELL function
How about
=IF(CELL("format",INDEX($A$1:$A$105,MATCH(B2,$A$1: $A$105,0)))="D9",INDEX($A$ 1:$A$105,MATCH(B2,$A$1:$A$105,0))/60,IF((LEN(INDEX($A$1:$A$105,MATCH(B2,$A$1 :$A$105,0)))-LEN(SUBSTITUTE(INDEX($A$1:$A$105,MATCH(B2,$A$1:$A$ 105,0)),":"," ")))=1,--("0"&INDEX($A$1:$A$105,MATCH(B2,$A$1:$A$105,0)) )/60,--("0"&INDEX($A $1:$A$105,MATCH(B2,$A$1:$A$105,0))))) although it doesn't update if the format changes (neither does your current) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) wrote in message oups.com... I have two formulas that I wish to nest. This formula automatically fixes the format of time if it is in the wrong format when exported from another system: IF(CELL("format",B2)="D9",B2/60,IF((LEN(B2)-LEN(SUBSTITUTE(B2,":","")))=1,-- ("0"&B2)/60,--("0"&B2))) eg: it will fix: :45 to 00:00:45 01:05 to 00:01:05 :12:34 to 00:12:34 Now where B2 is the value of a statistic which in this case is from an employee's time for something. I can use Vlookup to separately bring in the correct times from another workbook: VLOOKUP($A2,[All.xls]day1!$A$2:$M$105,2,FALSE) where A2 is the name of the employee in a manager's workbook and [All.xls]!$A$2:$M$105 is the employee's name and statistics. The problem is I need to combine these 2 functions together. So i need some way of returning the cell address B2 in the first function and when I have tried the address and match function it has not worked. eg. ADDRESS(MATCH(A2,[All.xls]day1!$A$1:$A$105,0),2) even though this formula on its own correctly returns B2 the nesting does NOT work and throws an error. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying the function contained within a cell to anouther cell. | Excel Worksheet Functions | |||
indirect function to reference cell on different sheet | Excel Worksheet Functions | |||
A function that returns the name of the current cell | Excel Discussion (Misc queries) | |||
Help: I need a function for finding next cell vertically with value | Excel Worksheet Functions | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions |