IF Array formula to create list from list

Question

I am trying to create a dynamic list which list only What follows this rule for a range of cells. The following formula works fine, but I don't want to test each row with a corresponding formula for each row. I have tried to set up an array without success. I have had limited success with arrays in the past.

=IF(AND(B1>0,C1=""),A1,"")

1. B1, C1 and A1 are each part of a specific name range for each column.
2. Each range is about 250 rows long, but most likely will never list more than 30 values from the A column.
3. This list will change as values are added to the C column.

Thanks

Answer 1

2. Yes the formula is on a different sheet in the workbook. The lists start in row 4 and the formulas start in the fifth row of a different sheet.        

Hi,

If you list starts on row 4, use:

=IFERROR(INDEX(Name,SMALL(IF((Start_First_Time>0)*(End_First_Time=""),ROW(Name)),ROW(1:1))-3),"")

(Ctrl+Shift+Enter).

or:

=IFERROR(INDEX(Name,SMALL(IF((Start_First_Time>0)*(End_First_Time=""),ROW(Name)),ROW(1:1))-ROW(Name)+1),"")

(Ctrl+Shift+Enter).

Cheers

Rich

Answer 2

Hi,

Try this array formula:

=IFERROR(INDEX($A$1:$A$7,SMALL(IF(($B$1:$B$7>0)*($C$1:$C$7=""),ROW($A$1:$A$7)),ROW(1:1))),"")

which must be committed with Ctrl+Shift+Enter.

Drag that formula down to display the returned list.

Take a look at http://office.microsoft.com/en-gb/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx for an explanation.

Cheers

Rich

Answer 3

Rich

This is what I was looking for but I don't understand how you got there from where I was.

This will be on a separate worksheet from the data.

Should the Row(1:1) reference be on my formula worksheet ?

SMALL(IF(($B$1:$B$7>0)*($C$1:$C$7="")

That's fascinating, I need to figure out how this works.

I will check back with you later.

Thanks again

Answer 4

Your help is greatly appreciated, but this Worksheet will be used by people that should not running macros so I need a formula solution.

I will carefully examine your contribution, because I am sure that I have another use for it.

Thanks again.

Answer 5

Hi,

try this code,

expected results in column K

data on Active sheet

in row 1 are headers

Sub macro_01()

Const myCol As String = "K"  '<<< export data in column K, changeRange(myCol & ":" & myCol).ClearContents

Dim ws As Worksheet

Set ws = ActiveSheet

Dim r As Long

ws.AutoFilterMode = False

r = ws.Cells(Rows.Count, "A").End(xlUp).Row

With Range("A1:C" & r)

.AutoFilter Field:=2, Criteria1:=">0"

.AutoFilter Field:=3, Criteria1:="="

End With

ws.Range("A2:A" & r).SpecialCells(xlCellTypeVisible).Copy ws.Range(myCol & "1")

ws.AutoFilterMode = False

End Sub