Gather information from a Mastersheet to individual worksheets - pending selection

I cant figure out how to do this.

I have a Master list of kids names and age groups, that will be used by Coaches to update, from this i would like to have each of the different age groups details extracted into different worksheets within the same workbook.

I used an IF statement to but found it was leaving blank lines between finds.

I have attached the spreadsheet - any help or hints or alternative methods are welcomed.

Peter

Hi,

Can you please read our forum rules.. then amend your title to something more descriptive of the issue at hand?

Thanks.

Try this formula in B6:

Please Login or Register  to view this content.

Which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Then copy down and across your chart.

The first part of the formula: IF(ROWS($A$1:$A1)>SUMPRODUCT(('MASTER LIST'!$H$7:$H$38="P2")+('MASTER LIST'!$H$7:$H$38="P3")) checks to see if the row number you're in is greater than the number of "P2" or "P3" entries...if it is greater then a blank is inserted (it's an error trap).

The next part: INDEX('MASTER LIST'!B$7:B$38,SMALL(IF(('MASTER LIST'!$H$7:$H$38="P2")+('MASTER LIST'!$H$7:$H$38="P3"),ROW('MASTER LIST'!$H$7:$H$38)-ROW('MASTER LIST'!$H$7)+1),ROWS($A$1:$A1)))

is like a vlookup for multiple matches... It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF(('MASTER LIST'!$H$7:$H$38="P2")+('MASTER LIST'!$H$7:$H$38="P3"),ROW('MASTER LIST'!$H$7:$H$38)-ROW('MASTER LIST'!$H$7)+1)

The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a "P2" or "P3" is found in range MASTER LIST'!$H$7:$H$38 and then it will return the corresponding row number within the range. The -ROW('MASTER LIST'!$H$7)+1 is added for robustness (in case you insert rows above, then the result won't skew).

The last ROWS($A$1:$A1) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA.

The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.

Then you copy down... The only thing that changes copying down is the ROWS($A$1:$A1) in the 2 occurances within the formula.. again to determine the step or row number we're in.

The only thing that changes when going across the table is what is Indexed (i.e. INDEX('MASTER LIST'!B$7:B$38..) and the specific item to use as a criteria (i.e. "P2" or "P3").

Hope this clarifies a bit.

Originally Posted by toeragh

OK. I will need to print this and read it over a couple of times to get in my head. For the other ages groups they will be single groups IE P4 or P5 or P6
P2 & P3 is the only joint one. So with that in mind ... when doing the line SMALL(IF(('MASTER LIST'!$H$7:$H$38="P2")+('MASTER LIST'!$H$7:$H$38="P3"),ROW('MASTER LIST'!$H$7:$H$38)-ROW('MASTER LIST'!$H$7)+1)

I will use SMALL(IF(('MASTER LIST'!$H$7:$H$38="P4"),ROW('MASTER LIST'!$H$7:$H$38)-ROW('MASTER LIST'!$H$7)+1)

Well here goes ... I maybe sometime getting my head round this - so will answer tomorrow.

Thanks. greatly appreciated.

Peter.

You should also then replace this SUMPRODUCT(('MASTER LIST'!$H$7:$H$38="P2")+('MASTER LIST'!$H$7:$H$38="P3") with COUNTIF('MASTER LIST'!$H$7:$H$38,"P4") in the error trap part of the formula.

You forgot to but a null for "value if true" part of the first IF() statement....

I.e.

Please Login or Register  to view this content.

I've copied up the sheet - there is obviously something not right with my excel (using Office 2007), as your last suggestion still does not work.

Please do not think that I am taking the rip here, I am honestly trying to get it to work. Using the formula evaluator, i can step through your formula and see what it going on. This has helped along with your description on how it works.

I have to make a copy of it and strip out the working worksheet, as its size is getting to large to post up here.

The other attachment shows what happens using the formula evaluator

Peter.

I think this time you forgot to confirm the formula with CSE keys.. as I did not see the { } brackets...

see attached.

Every time you edit a CSE formula (an array formula) you have to hold down the CTRL and SHIFT keys together and press ENTER. This makes the special brackets appear and therefore makes Excel understand that you have an array formula.

You can then copy the formula down and across as normal...the array aspect will copy too.

Have a look here for more info on Array formulas: http://www.cpearson.com/excel/ArrayFormulas.aspx

Great! Glad it all worked out.

Thanks for the feedback.