Copy cells from another sheet depending on category

**Lemac** · 04-14-2013, 04:59 PM

Hi I'm new here, unfortunately I won't be able to offer much help to others

I'm looking for a formula where a column can grab information from another sheet depending on the value of a corresponding value.

I have a list of all our sports club members and each fall into different categories - Child, Student, Adult and Senior.

I want to have a separate sheet for each category so the formula needs to look up the category and return the names from the corresponding cell.

I assume there is a straight forward formula but i cant seem to find it.

Many Thanks

**AB33** · 04-14-2013, 05:10 PM

I do not think a formula can create a separate sheet. A formula can only return a value. What you need is code, or macro. I can help if you could attach your sample. To attach a sample, go to advance then attachment.

**Fotis1991** · 04-14-2013, 05:15 PM

Perhaps this would be possible with an Array formula, but without a sample workbook we can not work..

**Tony Valko** · 04-14-2013, 06:47 PM

Here's a formula method.

Let's assume this is your data on a sheet named Data in the range A2:B15:

Name1.....Child
Name2.....Student
Name3.....Adult
Name4.....Adult
Name5.....Senior
Name6.....Senior
Name7.....Adult
Name8.....Adult
Name9.....Adult
Name10...Child
Name11...Senior
Name12...Student
Name13...Student
Name14...Adult

Create a sheet for each of the categories.

On each sheet enter these column headers:

A1: Category
B1: Count
C1: Name

In cell A2 enter the category for that particular sheet. For example, on the Child sheet enter Child as the category in cell A2.

Enter this formula in B2. This will return the count of records for the particular category.

=COUNTIF(Data!B:B,A2)

Enter this array formula** in C2. This will list the names for the category.

=IF(ROWS(C$2:C2)>B$2,"",INDEX(Data!A:A,SMALL(IF(Data!B$2:B$15=A$2,ROW(Data!B$2:B$15)),ROWS(C$2:C2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Here's a sample file that demonstrates this:

Lemac.xlsx