Copy Data to Sheet2 if cell in Sheet1 is not blank

[ph6971]

I have a workbook with 2 sheets.
Sheet1 has 3000 rows of marketing data, eg names addresses etc. It also
has columns for us to input whether a prospect has responded to a
mailshot and also whether we have placed any business for that
prospect.

Sheet2 is to be a record of every prospect that has responded. So, if a
cell in Column Z of Sheet1 contains data (ie is not blank) then I want
to copy the data from columns C,F,G,H to Sheet2.

I need to run through all rows in SHeet1 and then copy the data from
those rows that have data in Column Z into Sheet2.

If possible, I would like this to updated automatically so that when we
enter data into Sheet 1, Sheet 2 is updated.

I've tried to use Index / Match and I've messed with autofilters and
advanced filters but I've made no real progress and I need to get it
sorted if possible. Any help would be gratefully received.
Thanks in advance.

 

[Max]

One way?

Assume you have the names in col A, A2 downward in both Sheets1 & 2

In Sheet2

Put in C2
:
=IF(NOT(ISBLANK(Sheet1!$Z2)),OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,
COLUMN()-1,1,1),"")

Copy down col C

Copy formula in C2 to F2, then across to H2

Select F2:H2, copy down cols F:H

hth
Max