Populate column with non-blank cells from another column

Hi all,

Need some help with a formula I've been trying to work out...

I want to populate in a new sheet the contents of column E in the sheet named 'Surveys', where the adjacent column S is not blank.

I thought this formula would do it but it seems to find the first non-blank in S, list the adjacent cell in column E and then repeat this one when dragging down.

=IFERROR(INDEX(Surveys!$E$7350:$E$8080),SMALL(IF(Surveys!$S$7350:$S$8080)<>"N",ROW($W$7350:$W$8080)-ROW($W$7350)+1,""),ROW($W$7350:$W$8080)-ROW($W$7350)+1)),"")

Any help greatly appreciated.

Jason

If you are prepared to use a helper column in your first sheet, you could use much simpler formulae which are not array-entered. For example, you could use this in Z7350:

=IF(S7350<>"",MAX(Z$7349:Z7349)+1,"-")

and copy down. Then in your second sheet you could use this in A2:

=IFERROR(INDEX(Surveys!E:E,MATCH(ROWS($1:1),Surveys!Z:Z,0)),"")

although your test seems to be for <>"N" rather than <>"" in the first formula.

Hope this helps.

Pete

Hi Pete, thanks for the response.

It says <> "N" as I changed it to see if that would work as oppposed to <>"". No luck either way.

Unfortunately I can't edit the Surveys sheet as this is a template document that has been in operation for 5 months. This sheet gets moved into a report template that then looks up the necessary information.

Well, put the helper column in your second sheet in A2:

=IF(Surveys!S7350<>"",MAX(A$1:A1)+1,"-")

Then you can have this formula in B2:

=IFERROR(INDEX(Surveys!E:E,MATCH(ROWS($1:1),A:A,0)+7349),"")

to extract the data from column E of the Surveys sheet. Copy both down as far as needed.

You can hide column A if you wish to.

Hope this helps.

Pete

Just tried that method and works brilliantly, thanks a lot Pete. New trick to add to the arsenal.

Jason

You're welcome - thanks for feeding back.

Pete