Automatically create subset of records on different worksheet I have a table tha

Question

Automatically create subset of records on different worksheet

I have a table that has 14 columns and more than 850 records. One of the fields (column headings) is Category. Is it possible to set up different worksheets in which smaller subsets of the records in the main table could be automatically generated according to each category. So, you would have a main table in the first worksheet. In the second, you would have an Admin table, which is a subset of the main table in the Admin category that would be automatically generated and updated when a new record is added to the main table.

I don't think Lookup functions would work in this scenario and I am trying to think of a solution that does not involve VBA programming.

Just to clarify, this is like creating a several queries in an Access database that would be automatically updated when a record was added to the main table.

The question is: is this possible in Excel? Any ideas?

Explanation / Answer

> .. <
Here's a possible way to deliver it in Excel ...

Assume your 14 colsource data is in sheet: x,
cols A to N, data in row2 down,
with the key col = col F (Category)

List in P1 across the various categories, eg: Admin, etc
Put in P2: =IF(OR($F2="",P$1=""),"",IF($F2=P$1,ROW(),""))
Copy P2 across/fill down to cover the max expected extent of source data, eg down to row 1500?

Click Insert > Name > Define **
Put under "Names in workbook:":   WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK
**In Excel 2007, it should be Formulas > Defined Name

The above defines WSN as a name we can use to refer to the sheetname in formulas
It will auto-extract the sheetname implicitly (Technique from a post by Harlan)
Note: The workbook is presumed saved beforehand

Then, in a new sheet named after one of the Categories, eg: Admin
With the same col headers pasted into A1:N1
Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET(x!$O:$O,,MATCH(WSN,x!$P$1:$IV$1,0))),"", INDEX(x!A:A,MATCH(SMALL(OFFSET(x!$O:$O,,MATCH(WSN,x!$P$1:$IV$1,0)),ROWS($1:1)),OFFSET(x!$O:$O,,MATCH(WSN,x!$P$1:$IV$1,0)),0)))
Copy A2 across to N2, fill down to say N200, to cover the max expected extent for any Category. Cols A to N will return only the lines for the clategory Admin from the source sheet: x, with all lines neatly bunched at the top. Now, just dress the sheet up as desired, then make copies of it. Rename each copy as the other Categories (a one time job) and you'd get the corresponding results for each of those Categories. Adapt to suit ..

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.