Copy data from certain cells to other sheets

[markmac99]

Hi,

I have attached a screenshot of a workbook that I'm working on that needs to copy a number of cells to other sheets (Courses 1 to 4) if an 'x' appears on the booking form sheet in columns E to H. My current setup is to use =IF('Booking Form'!E2="x",'Booking Form'!A2,0) in Course 1 tab and F2, G2, H2 for each sheet thereafter, copying the formulas down each sheet. This works apart from the fact that the Course 1 to 4 tabs have gaps when there's no data to copy across (see image). Is there a simple way to adapt the IF formula or should I use code to copy the data from columns A to C across to the Course tabs?

Regards

Mark

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[markmac99]

Fluff,

I'm using MS Office pro Plus 2016.

 

[Fluff]

Thanks for that, how about in A2 copied down & across

=IFERROR(INDEX('Booking form'!A$2:A$12,AGGREGATE(15,6,(ROW('Booking form'!$A$2:$A$12)-ROW('Booking form'!$A$2)+1)/('Booking form'!$E$1:$H$1="Course 1")/('Booking form'!$E$2:$H$12="X"),ROWS(A$2:A2))),"")

 

[markmac99]

OMG, that works perfectly. I've just copied across all four sheets - changing the sheet name of course - and it's exactly what I was after.

Thank you very much Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[markmac99]

You're welcome & thanks for the feedback.

Fluff,

I am just about there with my spreadsheet but I've noticed that on occasion some staff members have to appear on two or more course sheets and not just the one. How could I tweak your formula to add additional criteria to look for an X in Course columns 1, 2, 3 and 4?

=IFERROR(INDEX('Booking form'!A$2:A$12,AGGREGATE(15,6,(ROW('Booking form'!$A$2:$A$12)-ROW('Booking form'!$A$2)+1)/('Booking form'!$E$1:$H$1="Course 1")/('Booking form'!$E$2:$H$12="X"),ROWS(A$2:A2))),"")

Kind regards,

Mark

 

[Fluff]

Did you change the "Course 1" to "Course 2" etc for each sheet?
If so, with the image in your op, Mrs Monkey should appear on both the Course 2 & Course 3 sheets.

 

[markmac99]

Did you change the "Course 1" to "Course 2" etc for each sheet?
If so, with the image in your op, Mrs Monkey should appear on both the Course 2 & Course 3 sheets.

Yes I did and I can see why this isn't making sense. My OP was a very simple table with four courses and your formula is spot on. The actual sheet that I'm working on is a little more complex, in that if a staff member has an X in one column - let's say course 1 - his details are then copied to other sheets that all form part of course 1. Course 1 could be maths, English and French, so that person's details would be booked onto all three sheets. All I need is a way to include other sheets in the formula and not just looking at one sheet. Is this a fairly tweak or would I need to start over?

Regards,

Mark

 

[Fluff]

You would be better off having a master sheet that includes all the data, which can then be pulled over to the other sheets.
Trying to check multiple sheets for data would not be simple & the formulae required to do that could well bog the workbook down.