+ Reply to Thread
Results 1 to 9 of 9

If entire column is blank, then

  1. #1
    Forum Contributor
    Join Date
    02-14-2013
    Location
    Biloxi, Ms
    MS-Off Ver
    Excel 2010
    Posts
    106

    If entire column is blank, then

    I have a chart with dates on a separate tab and I'm able to pull a date from that chart with criteria and transfer it to my main spreadsheet using the MIN IF function I requested in my last thread. However, if there are no dates at all that meet the criteria on the separate tab I get a "0" result. Formatted as a date, that results in "01/00/00."

    Since the ultimate goal is to find the earliest start date out of all the SAT results from both tabs, my formula returns that "01/00/00" and ignores any other dates that I have on my main tab. How do I get the result from the second tab to return nothing rather than a zero if there are no dates to retrieve from the column? Or, how do I get my final resulting formula to ignore the "01/00/00" result?
    Attached Files Attached Files
    Last edited by xrajncajnx; 05-28-2019 at 02:38 PM. Reason: Edited to add sample

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: If entire column is blank, then

    I'm assuming this is the thread that you are referring to.

    I'll try to do this without seeing a sample.

    Try this:
    =IFERROR(1/(1/MIN(IF((B2:B8="SAT")+(B2:B8="PART"),C2:C8))),"") Ctrl Shift Enter

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,855

    Re: If entire column is blank, then

    .
    Here is a macro approach for copying terms in Col A, from row 2:20. Adjust range as required.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: If entire column is blank, then

    Or using Aggregate

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    (this is an update to thread https://www.excelforum.com/excel-for...me-column.html)

  5. #5
    Forum Contributor
    Join Date
    02-14-2013
    Location
    Biloxi, Ms
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: If entire column is blank, then

    Quote Originally Posted by 63falcondude View Post
    I'm assuming this is the thread that you are referring to.

    I'll try to do this without seeing a sample.

    Try this:
    =IFERROR(1/(1/MIN(IF((B2:B8="SAT")+(B2:B8="PART"),C2:C8))),"") Ctrl Shift Enter
    I've edited to add a sample. Maybe you can help with my ultimate goal of getting a valid start date from either tab. The only way I've been able to do that is by building a formula on the Main tab to pull a date from the Chart2 tab, which is highlighted in yellow, and then build another formula to pull the Start Date, shown in red in cell G2 from all the results. As you will see, since Chart2 has no dates that meet the criteria of "PSAT" it returns "01/00/00" Which my start date formula reads as the earliest date. I'd prefer if I didn't have to transfer the date from the Chart2 tab, but I haven't been able to figure out how. I could probably change the data in cell E2 to an =IF(D2="0","","PSAT"), but given this record will be used by many people I can forsee too many formulas relying on each other getting messed up without my knowledge later down the road.

  6. #6
    Forum Contributor
    Join Date
    02-14-2013
    Location
    Biloxi, Ms
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: If entire column is blank, then

    Quote Originally Posted by Logit View Post
    .
    Here is a macro approach for copying terms in Col A, from row 2:20. Adjust range as required.
    I would love to be able to use a macro approach, but our firewall will not allow me to send it out.

  7. #7
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: If entire column is blank, then

    Hi
    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the file
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-14-2013
    Location
    Biloxi, Ms
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: If entire column is blank, then

    That appears to work, although I'm very unfamiliar with the aggregate formula. Always something new to learn with spreadsheets. Thank you.

  9. #9
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: If entire column is blank, then

    You are welcome.

    Thank you for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Deleting entire row if any specific column is blank
    By paradise2sr in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-21-2017, 11:27 PM
  2. [SOLVED] Delete Entire Row if Blank is Found in Column
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-17-2014, 11:28 PM
  3. [SOLVED] Delete entire row if Column C is blank
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2014, 12:03 PM
  4. [SOLVED] Delete entire row if column F is blank
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2013, 11:03 AM
  5. If Column U=Yes, copy entire row onto first blank row of new sheet
    By soldevi53 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2012, 05:35 AM
  6. Replies: 1
    Last Post: 02-09-2012, 12:55 PM
  7. Delete entire rows where there is a blank in column A
    By Chris Hankin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-11-2006, 05:00 AM

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