Named ranges as values of IF function

**goels** · 10-21-2011, 07:59 AM

Hi All,

I am trying to set a named range to be either one of two named ranges depending on an IF condition. So I have two named ranges called Abacus12 and Abacus24. Now if Abacus24 is empty I want to assign the named range Abacus to be 'Abacus12' and if not to Abacus 24.

The formula I am trying to define named range Abacus in name manager is

=IF(ISBLANK(Abacus24),Abacus12,Abacus24)

which is giving me a #NAME? error.

Any ideas how I can make this work?

Thanks

**NBVC** · 10-21-2011, 08:03 AM

Try:

=IF(COUNTA(Abacus24)=0,Abacus12,Abacus24)

**goels** · 10-21-2011, 09:34 AM

ISBLANK is not a problem, that works fine. The issue is excel does not seem to like giving named ranges as values in the IF formula; atleast not in the format I am trying. So how can I pass the named ranges Abacus12 or Abacus24 depending on the outcome of the IF function to the named range Abacus?

Thx

**NBVC** · 10-21-2011, 09:44 AM

The ISBLANK() only tests one cell.....

When I created named Range: Abacus with formula: =IF(COUNTA(Abacus24)=0,Abacus12,Abacus24)

I tested with formula in a cell: =COUNT(Abacus)

I entered numbers in my Abacus24 range and got expected result... I deleted those and entered numbers in Abacus12 range and got expected result.... If I have numbers in both ranges, the Abacus24 range took precedence.