Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search method
I am having trouble with the following formula:
=IF(SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"") Im using this to return the text and number (time) strings in 3 cells dependent on a text string being found within the text in the first cell "F5". My problem is that if f5 doesnt have a text string it returns #value and I dont want it to return anything!! Im intending using this repeatedly across a number of ranges to create a long list of text strings and blanks and want to then create a list of non blanks arranged in order time ANY IDEA HOW i MIGHT ACHIEVE THIS? I dont want to use an auto filter because I want the results to be static and not to require selection from a drop down! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
search method
This is ugly, but you might try it.
=IF(iserr(SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"")),"",SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"")) "Atishoo" wrote: I am having trouble with the following formula: =IF(SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"") Im using this to return the text and number (time) strings in 3 cells dependent on a text string being found within the text in the first cell "F5". My problem is that if f5 doesnt have a text string it returns #value and I dont want it to return anything!! Im intending using this repeatedly across a number of ranges to create a long list of text strings and blanks and want to then create a list of non blanks arranged in order time ANY IDEA HOW i MIGHT ACHIEVE THIS? I dont want to use an auto filter because I want the results to be static and not to require selection from a drop down! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
search method
Sorry, this might work better:
=IF(ISERR(SEARCH("staff1",'Main Board'!F5)),"",'Main Board'!G5 & " " & 'Main Board'!E5 & " " & 'Main Board'!F6) "TomPl" wrote: This is ugly, but you might try it. =IF(iserr(SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"")),"",SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"")) "Atishoo" wrote: I am having trouble with the following formula: =IF(SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"") Im using this to return the text and number (time) strings in 3 cells dependent on a text string being found within the text in the first cell "F5". My problem is that if f5 doesnt have a text string it returns #value and I dont want it to return anything!! Im intending using this repeatedly across a number of ranges to create a long list of text strings and blanks and want to then create a list of non blanks arranged in order time ANY IDEA HOW i MIGHT ACHIEVE THIS? I dont want to use an auto filter because I want the results to be static and not to require selection from a drop down! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
search method
Brilliant thanks v much
"TomPl" wrote: Sorry, this might work better: =IF(ISERR(SEARCH("staff1",'Main Board'!F5)),"",'Main Board'!G5 & " " & 'Main Board'!E5 & " " & 'Main Board'!F6) "TomPl" wrote: This is ugly, but you might try it. =IF(iserr(SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"")),"",SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"")) "Atishoo" wrote: I am having trouble with the following formula: =IF(SEARCH(staff1,'Main Board'!F5),'Main Board'!G5&" "&'Main Board'!E5&" "&'Main Board'!F6,"") Im using this to return the text and number (time) strings in 3 cells dependent on a text string being found within the text in the first cell "F5". My problem is that if f5 doesnt have a text string it returns #value and I dont want it to return anything!! Im intending using this repeatedly across a number of ranges to create a long list of text strings and blanks and want to then create a list of non blanks arranged in order time ANY IDEA HOW i MIGHT ACHIEVE THIS? I dont want to use an auto filter because I want the results to be static and not to require selection from a drop down! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT search versus other method | Excel Discussion (Misc queries) | |||
Search Folders Method | Excel Programming | |||
Refine search criteria for Find Method | Excel Programming | |||
better search method | Excel Programming | |||
Find Method ; search area | Excel Programming |