Excel Formula

Question

I'm hoping to find a formula that will take information from 1 page and input a name on another page in a workbook. What I have is a location tracking workbook, that on 1 page has a column for names and a seperate column with drop down menu for locations. On a seperate sheet I would like to be able to have a location column with everyones name under the location.

Answer 1

Lars,

it is working now, I want to apologize for all of these newbie questions, and thank you for your patience. I am only having problems with converting this to my layout. In the OP I had 2 columns, name and location, in the actual sheet I have 6. Column B is Name, Column F is the location. I am trying to decipher which part of the formula goes to which column.

 

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$B$2:$B$100,A$1),"",INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1 !$B$2:$B$100=A$1,ROW(A$2:A$100)),ROWS(A$2:A2))))

The way I have this changed to now is;

=IF(ROWS(A$2:A2)>COUNTIF(TRACKER!$F$2:$F$100,A$1),"",INDEX(TRACKER!$A:$A,SMALL(IF(TRACKER!$F$2:$F$100=A$1,ROW(A$2:A$100)),ROWS(A$2:A2))))

The only thing I have to do now is, on some of the Location page columns they need to be "recognized by their status, which is in column E of the tracker. So if I change the F to E on those columns I should get what I am looking for, right?

Thanks again.

 

OK, glad that is works now. You can use any "attribute" in your TRACKER sheet as headlines on your "Sheet2". Just make sure that you have the correct list of alternatives as headlines in the first row of  "Sheet2". And then you change the F to the respective column.

Regards / Lars-Åke

Answer 2

Put your names in column A of Sheet1, starting on row 1.

Put in a data validation in cell B1 of Sheet1 with the following Validation criteria:

Allow: List

In-cell dropdown: Checked

Source: =Sheet2!$A$1:$E$1     

Change the E so suit the number of locations you have

Copy cell B1 down as far as you have names in column A.

Put in all Locations on row 1 of Sheet2 starting on column A

Put the following formula in cell A2 of Sheet2:

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$B$1:$B$100,A$1),"",INDEX(Sheet1!$A$1:$A$100,

SMALL(IF(Sheet6!$B$1:$B$100=A$1,ROW(A$1:A$100)),ROWS(A$2:A2))))

Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

Change the 100 to fit the size of the data you have on Sheet1.

Copy cell A2 across all columns (locations) and down as far as needed.

Hope this helps / Lars-Åke

 

As you have a header row on Sheet 1 some modifications are needed:

Put your names in column A of Sheet1, starting on row 2 .

Put in a data validation in cell B2 of Sheet1 with the following Validation criteria:

Allow: List

In-cell dropdown: Checked

Source: =Sheet2!$A$1:$E$1     

Change the E so suit the number of locations you have

Copy cell B2 down as far as you have names in column A.

Put in all Locations on row 1 of Sheet2 starting on column A

Put the following formula in cell A2 of Sheet2:

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$B$2 :$B$100,A$1),"",INDEX(Sheet1!$A$2 :$A$100,

SMALL(IF(Sheet6!$B$2 :$B$100=A$1,ROW(A$2 :A$100)),ROWS(A$2:A2))))

Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

Change the 100 to fit the size of the data you have on Sheet1.

Copy cell A2 across all columns (locations) and down as far as needed.

 

Sorry gsf_77, this modification was not entirely correct. The $A$1:$A$100 should NOT be changed due to the headers (as the change of ROW(A$1:A$100) to ROW(A$2:A$100) takes care of the header).

Also, Sheet6 should of course be Sheet1. (When I tested the first version of this formula i had Sheet6.)

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$B$2:$B$100,A$1),"",INDEX(Sheet1!$A$1 :$A$100,SMALL(IF(Sheet1 !$B$2:$B$100=A$1,ROW(A$2:A$100)),ROWS(A$2:A2))))

And, as pointed out by Biff, we can simply use $A:$A instead of $A$1:$A$100.

So. here is a new attempt for formula in cell A2 of Sheet2. To be copied across and down.

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$B$2:$B$100,A$1),"",INDEX(Sheet1!$A:$A ,SMALL(IF(Sheet1 !$B$2:$B$100=A$1,ROW(A$2:A$100)),ROWS(A$2:A2))))

Hope this helps / Lars-Åke

Answer 3

Put your names in column A of Sheet1, starting on row 1.

Put in a data validation in cell B1 of Sheet1 with the following Validation criteria:

Allow: List

In-cell dropdown: Checked

Source: =Sheet2!$A$1:$E$1     

Change the E so suit the number of locations you have

Copy cell B1 down as far as you have names in column A.

Put in all Locations on row 1 of Sheet2 starting on column A

Put the following formula in cell A2 of Sheet2:

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$B$1:$B$100,A$1),"",INDEX(Sheet1!$A$1:$A$100,

SMALL(IF(Sheet6!$B$1:$B$100=A$1,ROW(A$1:A$100)),ROWS(A$2:A2))))

Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

Change the 100 to fit the size of the data you have on Sheet1.

Copy cell A2 across all columns (locations) and down as far as needed.

Hope this helps / Lars-Åke

Answer 4

yes, exactly. but the persons name can only show up once

Answer 5

I'm hoping to find a formula that will take information from 1 page and input a name on another page in a workbook. What I have is a location tracking workbook, that on 1 page has a column for names and a seperate column with drop down menu for locations. On a seperate sheet I would like to be able to have a location column with everyones name under the location.

It's not real clear what you want. So, here's my best guess...

You have data like this on one sheet:

Name1...Location3

Name2...Location1

Name3...Location3

Name4...Location2

Name5...Location1

Name6...Location3

Name7...Location2

Name8...Location1

Name9...Location1

Name10..Location1

And on another sheet you want to enter/select a location and then get this:

Location1

Name2

Name5

Name8

Name9

Name10

Is this what you want to do?

--

Biff

Microsoft Excel MVP