If Cell in certain column is value then show other related cells in a seperate sheet.

[Jenkinsakrb]

Hi all, 

i have a sheet attached https://krbnswedu-my.sharepoint.com/personal/jenkinsa_krb_nsw_edu_au/Documents/Diverse%20Learning%20... 

I only want to be updating data on the sheet titled main. I want the other sheets to sort through the data on main and display only the columns that I want. For example on the "Year 7" sheet i would like it to have column A B C L Y shown if column B has the number 7 in it. 

I want the other sheets to automatically update the data as i enter it on the Main tab. (used to be able to do this on Google sheets but now I am transitioning to excel I have struggled!)

Thanks legends, 

[#2]

@Jenkinsakrb That link doesn't work. At least not for me. Can you upload the file here?

[#3]

@Riny_van_Eekelen 

[#4]

@Jenkinsakrb Thanks. I trust that you can follow what I've done in the attached file. If not, let me know.

[#5]

Thanks heaps but this does not work when I add another row to the data in main sheet. EG if i added another name

[#6]

@Jenkinsakrb Ah, I see. You get a #SPILL error. That's because of the formulae in column D. Delete them. Then it should work. See attached.

[#7]

@Riny_van_Eekelen 

Sorry champ, no luck again. When I added another line i got this? 

See pic

[#8]

@Jenkinsakrb Aha! you are not using MS365 then or working in Excel for the Web. I, somehow assumed you were as you initially uploaded a link to a Sharepoint folder. I've jumped to conclusions. Am unable to suggest another approach right now. Perhaps later.