Copying specific cells ?

[brazen234]

If you please.....

I would like to do this:

If A1="XXXX" copy A2 to B1

If A22="XXXX" copy A23 to B2

If A29="XXXX" copy A30 to B3

I want to copy the very NEXT cell after any cell that has "XXXX" into
column in order.

Thanks

 

[Frank Kabel]

Hi
one way: enter the following array formula (entered with
CTRL+SHIFT+ENTER) in B1
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100="XXXX",ROW($A$1:$A$100)),ROW(1:
1))+1)
+and copy this down as far as needed

 

[brazen234]

Hi
one way: enter the following array formula (entered with
CTRL+SHIFT+ENTER) in B1
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100="XXXX",ROW($A$1:$A$100)),ROW(1:
1))+1)
+and copy this down as far as needed

Good news and bad news.
The actual formula does work but not for me.

I should have mentioned that the "xxxx" are actually numbers I'
working with, not text.

I made a list with text and the formula works fine but using numbers i
gives #NUM error.

Can you shed some light on this ?
Thank

 

[Frank Kabel]

Hi
if your number is for example the number 2 try
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100=2,ROW($A$1:$A$100)),ROW(1:1))+1
)

Note: Enter the number without apostrophes (if the number is stored as
real number)

You'll get the #NUM error if no more matches are found

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
one way: enter the following array formula (entered with
CTRL+SHIFT+ENTER) in B1
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100="XXXX",ROW($A$1:$A$100)),ROW(1:
1))+1)
+and copy this down as far as needed

--
Regards
Frank Kabel
Frankfurt, Germany

If you please.....

I would like to do this:

If A1="XXXX" copy A2 to B1

If A22="XXXX" copy A23 to B2

If A29="XXXX" copy A30 to B3

I want to copy the very NEXT cell after any cell that has "XXXX"

into

 

[brazen234]

Note: Enter the number without apostrophes (if the number is store
as
real number)Silly me! Why didn't I think of that ?

Thank you! It works