If same column in multiple sheets is marked with an X how can i extract surrounding data

Hi, today i have what i think is quiote a challenge for you all but i could be wrong.

A while a go you guys helped me with a formula whereby if on one sheet i have column A titled "name" and column b titled "in stock" and i put an x next to each the the names in stock on a separate sheet these names would populate in list order. the formula was as follows:

=IFERROR(INDEX(Sheet1!$B$4:$B$73,SMALL(IF($A$11<>Sheet1!$C$4:$C$73,ROW(Sheet1!$C$4:$C$73)-ROW($C$4)+1),ROW(1:1))),"")

THis worked brilliantly but now i need to use this formula to search over multiple sheets. Is this possible or will i have to amend the forumula to reflect each sheet. THis would be a pain as eventually will have 52 weeks as sheets.

I have attached the spreadsheet as an example. Basically if column T has an x in it i want to populate all the data on that row into the sheet titled Opperman. As you will see I have done this using the above forumla for week 3 data and then realised what a pain it would be to amend the forumlas for the following weeks so wiondered if the forumla could be tweaked to check all sheets or speciifed sheets.

Apologies if not explained very well.

I would suggest that you use a helper column in each subsidiary sheet which can identify the rows which match the criteria and set up a simple sequence running through the sheets. Then your summary sheet can just look at each number in sequence.

I am about to go out now, but if this has not been solved by someone else when I return then I'll take a further look at it.

Pete

Originally Posted by Pete_UK

I would suggest that you use a helper column in each subsidiary sheet which can identify the rows which match the criteria and set up a simple sequence running through the sheets. Then your summary sheet can just look at each number in sequence.

I am about to go out now, but if this has not been solved by someone else when I return then I'll take a further look at it.

Pete

Many thanks for your response and if it does notr get solved i look forward to hearing from you again.

I had just typed in a really lengthy reply to you, explaining all the ins and outs of the file I have developed, and then lost it. I'm not going to type it all out again, so I'll just attach the file and say that you can use 'Week 5' sheet as a template - copy this sheet as many times as you need and rename both the sheet tab and the name in cell B2. I've set formulae up so that it automatically adjusts each sheet from the previous week's sheet details.

Hope this helps.

Pete

Originally Posted by Pete_UK

I had just typed in a really lengthy reply to you, explaining all the ins and outs of the file I have developed, and then lost it. I'm not going to type it all out again, so I'll just attach the file and say that you can use 'Week 5' sheet as a template - copy this sheet as many times as you need and rename both the sheet tab and the name in cell B2. I've set formulae up so that it automatically adjusts each sheet from the previous week's sheet details.

Hope this helps.

Pete

Many thanks for this. Away on business today but will test as soon as back in the office. Sure will do what I am looking for but will report back to let you know the outcome etc so will leave as I solved until then.

Rep added now though for your hard work.

Originally Posted by Pete_UK

I had just typed in a really lengthy reply to you, explaining all the ins and outs of the file I have developed, and then lost it. I'm not going to type it all out again, so I'll just attach the file and say that you can use 'Week 5' sheet as a template - copy this sheet as many times as you need and rename both the sheet tab and the name in cell B2. I've set formulae up so that it automatically adjusts each sheet from the previous week's sheet details.

Hope this helps.

Pete

Hi Pete

Just done a few test copies and it seems to work perfectly. NOtice you made some other tweaks to the data as well which is much appreciated. Thanks you have saved me alot of hassle.

Thanks for getting back to me. It was all explained in the lengthy response that I lost, but hopefully you have been able to follow the logic of it.

Pete