Excel experts! please help me with this referencing issue?

Question

I have on the first page in Excel a list of names in one column and states they belong to in the next column. Eg. John Smith - New South Wales, Bob Jones - Queensland, Sally Brown - South Australia etc. What I want to do is the following pages is to have a separate page for each state (one for New South Wales, one for Queensland etc.) which references from this first page the names of those who belong to this state one after the other. I want it automatic, not using 'sort' or a macro - just a formula - even if I could do it in two steps to reach the outcome. I could use the "IF" function but it will leave blanks in the rows. Any ideas...?

IXL@XL · Accepted Answer

Cols A&B are your names and states (starting row2) In C1 enter Queensland, D1 enter NewSouth Wales etc. In C2 enter =IF($B2=C$1,ROW(),"") Copy across under states. Copy row 2 formulas down your list of names. On your sheet named Queensland enter in A1
 =IF(ISERROR(INDEX(Sheet1!A:A, SMALL (Sheet1!C:C,ROW()))),
"",INDEX(Sheet1!A:A, SMALL(Sheet1!C:C,ROW()))) Enter this same formula in cell A1 on all other sheets but change the column reference C:C to D:D etc for each one. If you wish to put the name into cell A2 adjust the formula to read ROW()-1

Andrew L · Answer

I very much doubt whether Excel can handle this. I would advise a database like Access. You can design a query and create reports for each state or for each person, at the press of a button.

? · Answer

Hightlight the placement you commit to place your header and click the Merge and middle button located on the an excellent way amazing edge of the show. this might guard your subject. John A+ qualified

Nick · Answer

is it really so much effort to just rewrite the names on each of the pages?

Trending News

Excel experts! please help me with this referencing issue?

4 Answers