how do you create a "search" button

Hi,

I have a workSheet that looks up a predefined set of numbers from a separate array, but it does it automatically once the numbers are defined in the cell.

I would like it to only look up the array once you press a "search" button - i.e define the numbers your looking for, then press "Search" and only then you would get the results?

Any suggestions that do not involve me having to have a ma

I would love to claim it as mine but I've got a lot of help from others here to get this far - I think i understand it:

{=IF(COUNTBLANK($I$2:$I$4)=3,"",(HYPERLINK(INDEX($P$2:$P$33,SMALL(IF(ISNUMBER(MATCH(1*LEFT($N$2:$N$33,$J$6),$I$6,0)),ROW($P$2:$P$33)-1,""),ROWS($1:1))),INDEX($O$2:$O$33,SMALL(IF(ISNUMBER(MATCH(1*LEFT($N$2:$N$33,$J$6),$I$6,0)),ROW($O$2:$O$33)-1,""),ROWS($1:1))))))}

it refers to the code in Cell I6 and returns all the matches from a separate list. It's great, but would be perfect if it didn't do it until told to...

Originally Posted by xladept

Hi Ing,

My advice was for a VBA routine. Instead - you could just set your calculation mode to manual and then when you're ready just hit the F9 key

I had no idea that was there!

tried it and it sort of works; don't think it's practical for me though, thanks anyhow...

Hi FDibbins,

I cannot get your code to work - it's only recognised as text?

that seems to work - must have been me. I think I'm going to create a search button in that added to the cell A1 when pressed then get it cleared with the others when the reset button is pressed. should work... thanks FDibbins

It worked! - just need to figure out now how to get rid of the #NUM return in some of the search result boxes when there's more boxes than results, then I'm done.

Thanks again.