IF statement trying to avoid blanks

**thoron6** · 03-31-2016, 05:31 AM

Afternoon all,

This will be a simple one for somebody out there but here goes.

I have 3 tabs (Summary, sheet 2, sheet 3)

All three tabs have the same columns in the same order. However, there is only data in the summary tab at the moment.

Name, Address, Town, Telephone number

These are the column headings. I want to do an IF statement that returns the rows IF the town matches a criteria.... So so far I have: "=IF('Summary'!$C:$C="Manchester",'Summary'!A2,"")"

This is bringing back the correct data but when I fill down it is including the blank rows. How can I get it to automatically add in the next row down when a certain town is entered in the summary tab? So each row is filled out with no blanks?

Thank you!

**Tony Valko** · 03-31-2016, 05:39 AM

Try something like this...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	Name1	Town1		Town1	Name1
3	Name2	Town3			Name4
4	Name3	Town2			Name6
5	Name4	Town1
6	Name5	Town4
7	Name6	Town1
8	Name7	Town2
9	Name8	Town3
10	Name9	Town4

This array formula** entered in E2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$10=D$2,ROW(B$2:B$10)),ROWS(E$2:E2))),"")

** 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.

IF statement trying to avoid blanks

LinkBack

Thread Tools

Rate This Thread

Display

IF statement trying to avoid blanks

Re: IF statement trying to avoid blanks

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Getting the 1st Non-Blank value - Trying to avoid using IF statement (If possible)

VBA combo boxes (ignore blanks + avoid duplicates)

nested if statement- trying to avoid but don't know how

Trying to avoid long IF statement.

How to avoid plotting blanks as zeroes...

Countif and averages avoid blanks

How to avoid counting blanks in a list

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions