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!

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!

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!

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!