If #REF then load to Query If #VALUE then don't. Need help with loading #REF records

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
66
Hello, Can you please help me find a workaround to load needed records

Is there a way to replace #REF error, but keep #VALUE one ?
Or in other way, Is there a way to load #REF records , but exclude #VALUE ones ?

If it helps I use the following formula in order to find keywords. And result with #REF means keywords was found. #VALUE means it wasn't.
Code:
=INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,E14))*ROW(list)))
I don't know if SQL, VBA, M may help, but I can also try them if no simpler solution available.
 
Last edited:

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
66
I found a bit easier to work with formula, so I would say this topic is closed, unless somebody wants to treat it as a challenge.
 

Forum statistics

Threads
1,078,486
Messages
5,340,622
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top