Copying cells from one sheet to another

[gcaullay1]

Hi there,

I am trying to create a register to capture lots of different details. On sheet 1 my team will input the details but in one column (I) it asks them a yes/no question.

If they answer yes to the question I would like the data in specific columns to come in to sheet 2 so they don't need to retype this data.

Below is the column titles in sheet 1 that I would like to be automatically populated in sheet 2.

Period Employee Name Age Line/Dept Role Shift ***

The forumla that I have used in each of the columns is - =IF(Register!I3="Yes",Register!A3,"")

It works but if they put NO in sheet 1 it leaves that row blank but this means one sheet 2 there will be gaps in the sheet when No has been given as an answer. Is there anyway so that when no is given as an answer it does not leave a blank row so that there are no gaps when the yes data filters through?

Thanks

[arlu1201]

It will be great if you attach a sample excel file.

[gcaullay1]

I think I have attached as it won't let me see any of the buttons.

[arlu1201]

Do you want a formula solution only? Will Vba work?

[gcaullay1]

Hi again,

To be honest I have never done that before and not sure what I would need to do?