Index or If formula - Not sure what to use

[JohnDear]

Posting again. Sorry I was notified I posted this in the wrong area earlier this week:

On sheet 1, Column A, is a list of names. Same name may be listed multiple times in column a. When a new name is listed on sheet 1, it also has to be entered one time on sheet 2 (report page) in column A.

Is there a formula that can be used on sheet 2, Column A, which will capture a name when it listed on sheet 1, so it doesn’t have to be keyed twice?

[Marcol]

See if this workbook helps

[JohnDear]

I inserted the formula and copied down a few rolls, but it gave me the same name in all rolls. The name that it inserted is the name that is listed on Sheet 1 in rolls 1-90. I believe the next name starts about roll 91.

[Marcol]

From your description I can't see how that's possible.

Can you post a sample of your workbook?

[JohnDear]

When I created the sample it worked. But I need to go one step further if possible. On Sheet 2 I only need the names with open accounts. Can another reference or array be added to this formula?

[Marcol]

That becomes a little more complex.
Perhaps it is best to create two dynamic named ranges

With "Sheet1"
Name:= Regarding_Party
Refers to:=

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A),1)

Name:= Status
Refers to:=

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A),1)

Then in "Sheet2" B2 this array formula

=IFERROR(INDEX(Regarding_Party,ROW(IF(ISERROR(INDEX(Status, SMALL(IF((Status<>"Open")+ISERROR(Status), "", ROW(Status)-MIN(ROW(Status))+1), ROW(1:1)))),"",INDEX(Status, SMALL(IF((Status<>"Open")+ISERROR(Status), "", ROW(Status)-MIN(ROW(Status))+1), ROW(1:1))))),1),"")

Confirm with Ctrl+Shift+Enter not just enter
Drag/Fill Down

[JohnDear]

Thanks...problem solved.

[Whizbang]

You can use this technique to retrieve unique values from a column.

http://www.get-digital-help.com/2009...om-one-column/

[JohnDear]

Thanks for the link.