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?
|