IF/INDIRECT Limitation

  • Thread starter Thread starter durwood
  • Start date Start date
D

durwood

I'm trying to find a shortcut that will allow me to copy a text string
when ever the cell equals "*" and place into beside the "*" in another
column. The problem is the text i need to copy varies and the number
and spacing of "*" also changes. I've used a combination of IT/INDIRECT
functions but with limited luck.

This is a sample of the spreadsheet and my desired result:

NOTE

Theres 0000's of lines to complete hence the shortcut.


+-------------------------------------------------------------------+
|Filename: Sample Problem.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4529 |
+-------------------------------------------------------------------+
 
Hi Durwood,

I am not sure that I understand your question correctly. If in A1 you have
text, and in b1 you have an asteriks, then in c1 you could type:
=IF(B1="*",A1,"")

Otherwise, if you will have any number of "*" separated by any kind and
amount of characters or spaces, then you could try in cell c1:
=IF(ISERROR(IF(1<=(SEARCH("~*",B1)),A1,""))=TRUE,"",A1)

Hope this helps.
 
Hi BigPig,

Sorry properly didn't make it clear enough. In the sample pic, column
A represented what i wanted the data to look like once some "formula"
was used on column B. So the text would be captured and placed next to
its 'corresponding' asterisk.

Hope that helps. :)
 
Hi Durwood,

You could put the following in cell B2:
=IF(A2="",A3,(IF(ISTEXT(A2)=TRUE,"*","")))

Then go into 'tools','options','view' (tab), uncheck 'Zero Values'

Have a sunny day.
 
Durwood,

having seen your attachment picture and followed the thread, I believe
the formula that you need for A3, to copy down, is the following
*array* formula (i.e. must be committed with Shift+Ctrl+Enter):

=IF((B3="")+(B3<>"*"),"",OFFSET(B1,MAX(IF(($B$1:B3="")+($B$1:B3="*"),FALSE,ROW($B$1:B3))),0))

I will be going now, so maybe someone else might jump in, if
explanations are needed. I will review the thread in the morning.

HTH
Kostis Vezerides
 
Back
Top