Array formula to copy cells

[MagicMan]

Hi Guys,

I'm trying to achieve a process whereby if a cell in Sheet1 column P (from cell P3) downwards is not blank it will be copied into sheet 3 cell a2 downwards. If it is blank it won't.

I have come across an array formula

Please Login or Register  to view this content.

But I don't know to change it to make it relevant.

An alternative solution I've found was this:

Let's input the desired item into A1, eg: Chain
Put this in A2:

Please Login or Register  to view this content.

Copy A2 down to cover the max expected extent of source data in Sheet1, say down to row 500?
Then place this in B2:

Please Login or Register  to view this content.

Copy B2 to C2, fill down. Cols B & C will return the required results depending on the item input in A1, all neatly packed

But this assumes I want to match Cell A1 with a specific value as opposed to 'not blank'.

I know I can do it with a macro, but if anyone can help achieve it with a formula, I'd be very grateful.

many thanks

Terry

[NickyC]

hi
assuming your data are in cells P3:P20, try this (not an array formula)

Please Login or Register  to view this content.

[MagicMan]

Brilliant Nicky! That was perfect - Reputation added