nest functions in CELL function

[deekaye]

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?

 

[Bob Phillips]

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)