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

[NBVC]

What about A14 to E16? Does that have to come across too?

[Backroomgeeza]

Yes.... everytime the code 'S2' appears in column A, i need to somehow return the data in the associated range.

So when 'S2' appears in cell A10 in need the range A10-E12

and when 'S2' appears in cell A14 i need the range A14-E16

and so on...... i could well be looking at 100 of these ranges per code

[NBVC]

Try this for size:

In the data sheet, F2 insert formula:

=LOOKUP(REPT("z",10),A$2:A2)&"_"&COUNTIF(F$1:F1,LOOKUP(REPT("z",10),A$2:A2)&"*")+1

copied down the column

In the candyballs sheet in A2:

=IFERROR(INDEX(data!A:A,MATCH("S2_"&ROWS($A$1:$A1),data!$F:$F,0)),"")

copied down as far as needed and across 5 columns.

You can format the cells as Custom: 0;-0;;@ to hide the 0's that show up for blanks.

[Backroomgeeza]

I will try this when i get back from the gym in an hour..

if it works i owe you a cyber pint of some sort

thanks for your help

[Backroomgeeza]

It works.... you legend... thanks soooooo much