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