Formula to bring in a cell range from a cell reference

[Backroomgeeza]

Hi

I would be grateful if someone could come up with the solution to my problem.

example.jpg

Basically as you can see (hopefully) from the picture attached, there are 5 columns containing data. (the actual worksheet for this can run into hundreds of different records spread over about 20 different codes). The aim for me is to have a separate worksheet for each code. But in order to do this i need to be able to find a way of extracting the data from this main sheet for each code.

For example.... from the picture I want to extract that data for all records for code S2, which has a variant of candyball and put it into a worksheet called candyballs (S2 will ALWAYS have this variant)... So the first occurrence of S2 is at cell A10 (but each week the location of where this appears will be different).. so i think it needs to be some sort of lookup..... the range of data for this record i need to bring across is from A10 to E12.

so i am looking for a certain bit of data in cell A10, then if it is present I want to return a range of cells.

the idea is then that each week i can paste all of the data into the main data sheet and the formulas in the code specific sheet will automatically update the data for me.

is this possible?

I have also attached a copy of the spreadsheet in case that helps as well.... as i said.. any help on this would be greatly appreciated