How to Define #REF and #VALUE errors

I need to create a formula that can differentiate between these 2 errors. I have tried a lot of different methods so far, but none of them will differentiate between the different types of errors.

I do not know how to use the VB code in excel or even where it is, so I need to find out if I can do this without using it.

I have a series of combobox dropdowns that rely on input from previous selections, some rely on multiple previous variables. If the error is #REF it means that the index has been put out of range and should display "-----" (since the "ignore blank" doesnt seem to work when you return an error or have a function returning a blank in a cell), and if it is "#VALUE it means the selections made have built a combination of objects that are larger than the allotted volume and needs to display "Volume too Large".

Combo1 Index   Combo2 Index

A1                        B1

A2                        B2

A3                        B3

A4                        B4

If A1 is selected, B1, B2 & B3 will show up with B4 being "-----" (obviously stating not to choose it).

If B1 is selected, B1 will display "Volume Too Large", B2 will show up, and B3 anc B4 will display as "-----"

Thanks for any help.

Try the error.type() function. I'd have a vlookup table that returns the correct message based on the number that ERROR.TYPE returns.

From the help file:

I think I am missing something basic.

=IF(AE216=(ERROR.TYPE(#REF!)
),"-----",(IF(AE216=(ERROR.TYPE(#VALUE!)),"Volume too Large","")
))

This seems to execute, but i have to find a way for the formula to read #REF! and #VALUE!.

I tried a =VLOOKUP(#REF!, AF216:BF217,2,False), but it has the same problem with the logic #REF! - this obviously wouldnt return a value, it = #N/A!

#REF!       1

#VALUE!   2

Do I have the end of that formula right to display the original value? I think, basically, I need it to state, IF cell has #ref, or #value, return "whatever", if not then dont do anything. I hope, to get that result, you just omit the if not section.

Rewriting your formula, you might end up with something like this.

=IF(ERROR.TYPE(AE216)=4,"-----",IF(ERROR.TYPE(AE216)=3,"Volume too Large",""))

omg, thanks!

I feel kinda stupid now. I thought you meant to manually create an index assigning a value to the error type....... I didn't realize you were saying they were already defined heh.

It wouldnt return the original cell info if there was no error, so I had to follow-up by adding an ISERROR() before the formula.

Thanks again, works great!

Good. I was hoping that a nudge in the right direction would get you going. I've never actually used ERROR.TYPE in any of my spreadsheets, so it was fun to test it out.