Auto filling different sheets from one master sheet

Hi

In short I am trying to see if it is possible to automatically fill in separate sheets based on someone typing YES into one cell

In order to save a lot off time by retyping information staff have already inputted, can i copy this across.

This probably doesn't really make sense, but i hope the attached example makes it easy to understand.

Thanks for any help

Hello,

In cell A2 of sheet H&S:

=IFERROR(INDEX(Main!$A$2:$A$15, SMALL(IF(Main!$F$2=Main!$B$2:$B$15, ROW(Main!$B$2:$B$15)-ROW(Main!$B$2)+1), ROW($A1:$A1))),"" )

CTRL+SHIFT+ENTER to make it an array and drag down. This will pull all of the Staff IDs for the H&S course that had a "Yes" from the main sheet.

Then in cell B2:

=IFERROR(INDEX('Personal Info'!B$2:B$21,MATCH('H&S'!$A2,'Personal Info'!$A$2:$A$21,0)),"")

This is a non-array formula that you can drag across and down for the H&S sheet.


Do the same for the other sheets. Notice the first formula cell F2 was used. I opted to have this cell with a keyword that now has "Yes". You can put an "X" there or whatever you choose to select the course.

Good luck with your project

Hello Spencer,

Here's another option using VBA:-

Please Login or Register  to view this content.

I've attached your sample workbook with the code implemented and assigned to the "RUN" button. Click on the "RUN" button to see it work. To tidy things up a little, I've also removed blank columns from the main sheet.

I hope that this helps.

Cheerio,
vcoolio.