Adding if error to a formula
 

Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0))

I know to enter ctrl-shift-enter. However if I have not entered data yet
for one of the fields the formula is referring to I get: #N/A.

I would like the cell to be blank if the reference cells are blank.

Thank you

Adding if error to a formula
 

Hi Jim

One way
=IF(COUNTA(D1:D2)<2,"",your_formula)

--
Regards
Roger Govier

"Jim" wrote in message
...
Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0))

I know to enter ctrl-shift-enter. However if I have not entered data yet
for one of the fields the formula is referring to I get: #N/A.

I would like the cell to be blank if the reference cells are blank.

Thank you

Adding if error to a formula
 

That did not work. Any other suggestions. Oh, and thanks for helping me.

"Roger Govier" wrote:

Hi Jim

One way
=IF(COUNTA(D1:D2)<2,"",your_formula)

--
Regards
Roger Govier

"Jim" wrote in message
...
Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0))

I know to enter ctrl-shift-enter. However if I have not entered data yet
for one of the fields the formula is referring to I get: #N/A.

I would like the cell to be blank if the reference cells are blank.

Thank you

Adding if error to a formula
 

How did it not work?

It looks like it should.

Jim wrote:

That did not work. Any other suggestions. Oh, and thanks for helping me.

"Roger Govier" wrote:

Hi Jim

One way
=IF(COUNTA(D1:D2)<2,"",your_formula)

--
Regards
Roger Govier

"Jim" wrote in message
...
Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0))

I know to enter ctrl-shift-enter. However if I have not entered data yet
for one of the fields the formula is referring to I get: #N/A.

I would like the cell to be blank if the reference cells are blank.

Thank you



--

Dave Peterson

Adding if error to a formula
 

getting the same error. Still ctrl-shift-enter, right?

"Dave Peterson" wrote:

How did it not work?

It looks like it should.

Jim wrote:

That did not work. Any other suggestions. Oh, and thanks for helping me.

"Roger Govier" wrote:

Hi Jim

One way
=IF(COUNTA(D1:D2)<2,"",your_formula)

--
Regards
Roger Govier

"Jim" wrote in message
...
Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0))

I know to enter ctrl-shift-enter. However if I have not entered data yet
for one of the fields the formula is referring to I get: #N/A.

I would like the cell to be blank if the reference cells are blank.

Thank you



--

Dave Peterson

Adding if error to a formula
 

Yep.

But the error could be caused by not finding a match--not because you haven't
entered the data.

=IF(COUNTA(D1:D2)<2,"not enough data",
IF(ISNA(MATCH(1,(Calls_Received_All!B1:B10000=D2)
*(Calls_Received_All!C1:C10000=D1),0)),"no match",
INDEX(Calls_Received_All!D:D,
MATCH(1,(Calls_Received_All!B1:B10000=D2)
*(Calls_Received_All!C1:C10000=D1),0))))

Still an array formula.

When you get it working, change both those strings to "" (if you want).

Jim wrote:

getting the same error. Still ctrl-shift-enter, right?

"Dave Peterson" wrote:

How did it not work?

It looks like it should.

Jim wrote:

That did not work. Any other suggestions. Oh, and thanks for helping me.

"Roger Govier" wrote:

Hi Jim

One way
=IF(COUNTA(D1:D2)<2,"",your_formula)

--
Regards
Roger Govier

"Jim" wrote in message
...
Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0))

I know to enter ctrl-shift-enter. However if I have not entered data yet
for one of the fields the formula is referring to I get: #N/A.

I would like the cell to be blank if the reference cells are blank.

Thank you



--

Dave Peterson


--

Dave Peterson

Adding if error to a formula
 

That was perfect - thanks

"Dave Peterson" wrote:

Yep.

But the error could be caused by not finding a match--not because you haven't
entered the data.

=IF(COUNTA(D1:D2)<2,"not enough data",
IF(ISNA(MATCH(1,(Calls_Received_All!B1:B10000=D2)
*(Calls_Received_All!C1:C10000=D1),0)),"no match",
INDEX(Calls_Received_All!D:D,
MATCH(1,(Calls_Received_All!B1:B10000=D2)
*(Calls_Received_All!C1:C10000=D1),0))))

Still an array formula.

When you get it working, change both those strings to "" (if you want).

Jim wrote:

getting the same error. Still ctrl-shift-enter, right?

"Dave Peterson" wrote:

How did it not work?

It looks like it should.

Jim wrote:

That did not work. Any other suggestions. Oh, and thanks for helping me.

"Roger Govier" wrote:

Hi Jim

One way
=IF(COUNTA(D1:D2)<2,"",your_formula)

--
Regards
Roger Govier

"Jim" wrote in message
...
Hello, I'm currently using this formula, which works perfectly :
=INDEX(Calls_Received_All!D:D,MATCH(1,(Calls_Recei ved_All!B1:B10000=D2)*(Calls_Received_All!C1:C1000 0=D1),0))

I know to enter ctrl-shift-enter. However if I have not entered data yet
for one of the fields the formula is referring to I get: #N/A.

I would like the cell to be blank if the reference cells are blank.

Thank you



--

Dave Peterson


--

Dave Peterson