Mr Excel & excelisfun Trick 18: IF function with FIND SEARCH AND OR ISERROR & ISNUMBER functions

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Hi,

I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10 :R10 )),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

Any idea?

Any help much appreciated!

Best regards

Nick

I am having trouble getting a few conditions to work when I join them, close but not quite there yet.

I need it to say if D19 is greater than 40 and C28 is "HSA 8", than 2500 OR if D19 is greater than 40 and C28 is "HSA 3" than 1500.

Then the second part needs to say if D19 is less than 40 and C28 is "HSA 8" then 2500 * D19/40,0

Then the last statement should say if D19 is less than 40 and C28 is "HSA 8" AND D28 is "individual" then 1500 * D19/40,0....all other conditions should be zero.

Below is my attempt at the first part one and part 3 of the formula. Any suggestions?

IF(D19>40,IF(ISNUMBER(SEARCH("HSA 8",C28)),2500,IF(D19>40,IF(ISNUMBER(SEARCH("HSA 3",C28)),1500,0),IF(ISNUMBER(SEARCH("HSA 3",C28)),IF(D19

Hi

I have recorded the below code, is it possible to adjust this so that instead of using "USD" the macro will reference whatever currency the user inputs into say sheet1 cell A1?

thanks

Code:

"=IF(OR(RC[-3]=RC[-2],ISNUMBER(SEARCH(""USD"",RC[-1]))),""ok"")"

Never mind - I got it!!

Essentially I want the cell with the function to search a group of cells for "MS043"

and if "MS043" appears anywhere within the group of cells (whether it be on its own if one of the cells, combined with other info in a cell, be in more than one cell etc...) to return "Y" and if it is not in the group of cells return "N"

I'm Using

=IF(ISNUMBER(SEARCH("MS043",C240:C254)),"Y", "N")

and have no luck

When i use

=IF(ISNUMBER(SEARCH("MS043",C254)),"Y", "N")

it works so only one cell it works fine, so i figure its something to do with excel thinking "ms043" must appear in every cell to return a "Y"

Any ways around this?

Thanks

=IF(ISERROR(FIND("letter of credit",OR(O16,P16),1))=TRUE,"NO","YES")

I tried to use OR(O16,P16) to get the formula to search for both O16 and P16 to check if either cell contains "letter of credit."

The original formula works:
=IF(ISERROR(FIND("letter of credit",O16,,1))=TRUE,"NO","YES")

where this only searches one cell O16.

Are there other ways to construct this keyword search?

I am trying to use the ISERROR or ISnumber function I have posted the formula i have been using below but with isnumber inserted it just gets a max of 0 with iserror inserted I get #value error I just need some help sorting this out.
=MAX(IF(ISNUMBER((($B$2:$Q$2))),(($B$2:$Q$2)),0))

Putting together insights I've recently gained from ubermoderator Rob, it appears I want a formula looking something like this:

=IF(AND(ISERROR(SEARCH("Blood and Gore",AY2)),ISERROR(SEARCH("Animated Blood",AY2)),ISNUMBER(SEARCH("Blood",AY2))),1,0) +IF(ISNUMBER(SEARCH("Mild Blood", AY2)), 1,0)

In this example, I'm trying to recode textual reference to "Blood" or "Mild Blood" in AY2 as 1 and 2, respectively, while avoiding unwanted TRUE responses for "Animated Blood" and "Blood and Gore," which should both return FALSE responses of 0. So basically, I'm looking to define the phrases "Blood and Gore" and "Animated Blood" as error terms while summing two ISNUMBER searches to create a sort of 3rd response ("2") to the whole =IF logic test.

Is there a basic syntax error in the formula above that's preventing this, or am I more fundamentally moving in the wrong direction with this attempted solution?

Supercheers!

Gravy