Removing #REF! indirect index function

**dyerdyerdyer** · 04-08-2011, 01:14 AM

Hi,

I'm looking to remove the #REF! result from this function. I'm aware that I might be able to use ISNA or Iserror but i cant seem to get it to work so far.

This is my formula at the moment.

=IF($A$20="","",INDEX(INDIRECT($A$20),ROW(A7)))

Thanks

Mark

**protonLeah** · 04-08-2011, 01:41 AM

What's in A20 and where does it come from?
Since Row(A7)=7, what's the point of that?

**dyerdyerdyer** · 04-08-2011, 01:52 AM

thank you for your quick reply, i have attached the spreadsheet, if you choose between 'Narrow' and 'Antique' in cell A20 you can see the #REF! results shown. I know they are there because there is only one value in the named range 'Antique' but is there a way they can be removed?

**protonLeah** · 04-08-2011, 02:28 AM

A20 would have to be a formula that results in a cell address not a data value. That is, the INDIRECT function uses one cell as the pointer to another cell.

Suppose you have the value 100 in cell K31 because of some math result and in C100 you have 13.

Now suppose that you have, in cell, say D3: =INDIRECT("C" & K31) ---> The result in D3 is =INDIRECT(C100) which will display 13

Since you have the word Antique in cell A20, you get the #Ref error, Antique not being a cell address...

**kafukalatrava** · 04-08-2011, 03:46 AM

My easy workaround for your case goes something like this:

if(iserror(your formula)= true," ",your formula)

you basically copy and paste "your formula" , whatever it is as logicla test and as reult when logical test is false. when it gives error, you choose to show whatever you want, when it's not error, it shows your normal formula result.
so this line of thought works fine most of the times for me. I used " " (space character) so it becomes invisible for the user, but you could use 0 as well if the cell is involved in calculations. you may omit "=true" in the logical test.

Originally Posted by dyerdyerdyer

Hi,

I'm looking to remove the #REF! result from this function. I'm aware that I might be able to use ISNA or Iserror but i cant seem to get it to work so far.

This is my formula at the moment.

=IF($A$20="","",INDEX(INDIRECT($A$20),ROW(A7)))

Thanks

Mark

**DonkeyOte** · 04-08-2011, 04:04 AM

Originally Posted by dyerdyerdyer

if you choose between 'Narrow' and 'Antique' in cell A20 you can see the #REF! results shown. I know they are there because there is only one value in the named range 'Antique' but is there a way they can be removed?

The issue in this instance is that the Named Range being evaluated has fewer rows than being referenced - ie Antique refers only to one row.

Above is 100% accurate - there are a few approaches you could adopt to handle the errors:

a) double evaluate the formula in it's entirety along the lines outlined in prior post (note use of =TRUE is superfluous):

Please Login or Register  to view this content.

b) double evaluate the INDIRECT element only (ie validate rows)

Please Login or Register  to view this content.

c) if you know the result is always text you can avoid the double evaluation via a LOOKUP & CHOOSE construct

Please Login or Register  to view this content.

For the remaining calculations in Col C I would use either

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

If you want to display 0 for the missing items use the latter item and remove the pre-emptive IF

One final pointer...

If your source Defined Names are ever to become Dynamic then INDIRECT will cease to work in the above form
(in other words were "Antique" defined using OFFSET or INDEX construct rather than a hard wired range (such that it resized itself automatically) a basic INDIRECT approach will no longer suffice)