+ Reply to Thread
Results 1 to 4 of 4

IF function that eliminates empty rows

  1. #1
    Registered User
    Join Date
    09-29-2006
    Posts
    18

    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?

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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

  3. #3
    Registered User
    Join Date
    09-29-2006
    Posts
    18

    couldn't make it work

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

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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.

    ---
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1