IF function that eliminates empty rows

I need to pull data (company names and codes) from rows on "tab 1" to three other tabs and pull that information based upon a dropdown list (data validation) with three choices on each row (one tab for each dropdown choice). The data will flow to the back tabs depending on the dropdown. The IF function on the three other tabs is easy; however, I end up with lots of blank rows. I can't use a static method such as sort. I can't use a nested IF function because there are 65 lines. I want the data to appear without all of the blank rows. I am stuck on this one. How do a get rid of the blank rows, yet have a "realtime" info on the back tabs?

well you copy the data and have this on a macro where it updates if the value in the validation cell changes, or you use an array formula

entered with shift ctrl enter


=OFFSET(A3,SMALL(IF($C$3:$C$13=$H$1,ROW(C3:C13),""),2),0)

if h1 was where the dropdown is, return the row, this can be used in an offset function to return the data. If you post the data, it would be easier, to give a better answer.

regards

Dav

Originally Posted by Dav

well you copy the data and have this on a macro where it updates if the value in the validation cell changes, or you use an array formula

entered with shift ctrl enter


=OFFSET(A3,SMALL(IF($C$3:$C$13=$H$1,ROW(C3:C13),""),2),0)

if h1 was where the dropdown is, return the row, this can be used in an offset function to return the data. If you post the data, it would be easier, to give a better answer.

regards

Dav

I've tried this many ways and I can get it to return data, but only the smallest value. {=OFFSET(Cost!A1,SMALL(IF(Cost!J14:J18="MSC",ROW(Cost!J14:J18),""),1),0)}

The biggest reason is that I don't understand the SMALL function. Perhaps I should explain again - what I need to do is simply get rid of blank rows. The only problem is the rows with the data have an IF function and therefor arn't really empty.

I tried this:=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

Again, I got a blank for no return on the IF function.

Originally Posted by abrazee

I've tried this many ways and I can get it to return data, but only the smallest value. {=OFFSET(Cost!A1,SMALL(IF(Cost!J14:J18="MSC",ROW(Cost!J14:J18),""),1),0)}

The biggest reason is that I don't understand the SMALL function. Perhaps I should explain again - what I need to do is simply get rid of blank rows. The only problem is the rows with the data have an IF function and therefor arn't really empty.

I tried this:=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

Again, I got a blank for no return on the IF function.

I managed to get

=IF(ROW()>5+(COUNTIF(C$3:C3,H$1)),"",OFFSET($A$1,SMALL(IF($C$3:$C$13=$H$1,ROW(C$3:C$13),""),(ROW()-2))-1,0))

ctrl-shift-enter

working - see attached.

---