+ Reply to Thread
Results 1 to 12 of 12

=MIN *Custom Message When Null*

  1. #1
    Registered User
    Join Date
    05-13-2022
    Location
    Florida, USA
    MS-Off Ver
    365
    Posts
    11

    Question =MIN *Custom Message When Null*

    Hello I am new here, I have created a sheet that looks up the oldest date in a column from another page. However if there are no dates in that column it displays "01/00/00"

    This is the formula I am using: =MIN(SheetName!N:N)

    What I would like is for it to display either "N/A" or just a null cell if there is no date in the page column I am referencing.

    Tried some IFELSE and IFERROR combinations but they only seem to work with VLOOKUPs

    Any suggestions? Thanks!
    Last edited by PinkFloydEffect; 05-27-2022 at 05:14 PM.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,699

    Re: =MIN *Custom Message When Null*

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-13-2022
    Location
    Florida, USA
    MS-Off Ver
    365
    Posts
    11

    Re: =MIN *Custom Message When Null*

    Here you go
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,441

    Re: =MIN *Custom Message When Null*

    Try this:

    =If(Count(SheetName!N:N)=0,"N/A",MIN(SheetName!N:N))

  5. #5
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,522

    Re: =MIN *Custom Message When Null*

    If it's just for display purpose, then use cell formatting: mm/dd/yy;mm/dd/yy;"null";@.

    cell formatting.png

  6. #6
    Registered User
    Join Date
    05-13-2022
    Location
    Florida, USA
    MS-Off Ver
    365
    Posts
    11

    Re: =MIN *Custom Message When Null*

    Quote Originally Posted by Phuocam View Post
    Try this:

    =If(Count(SheetName!N:N)=0,"N/A",MIN(SheetName!N:N))
    Thank you! This did exactly what I was trying to accomplish

  7. #7
    Registered User
    Join Date
    05-13-2022
    Location
    Florida, USA
    MS-Off Ver
    365
    Posts
    11

    Talking Re: =MIN *Custom Message When Null*

    Now I have another related request - I am not having any luck modifying this function to work on cells D6-D9 (Daily Tickets Report Sheet).

    This one is a bit different as the format is not a date rather "# days ago"

    Modified the function provided above by Phuocam by changing the sheet name and column which works when the cell its referencing is null or in a #/#/# date form - but not when entered as "# days ago"

    As you can see the format I have been using for D6-D9 is: =(CallTek!Q2)

    So I would like to either display "# days ago" or "N/A" if the cell is null....however ULTIMATELY would be nice to somehow convert the days ago to a #/#/# date form which I assume would require setting up a separate function on the sheet I am referencing from first?
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,270

    Re: =MIN *Custom Message When Null*

    It seems to me that when the corresponding value in column B is zero that the cell in column D should display "N/A", therefore I suggest the following for D6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    05-13-2022
    Location
    Florida, USA
    MS-Off Ver
    365
    Posts
    11

    Re: =MIN *Custom Message When Null*

    Quote Originally Posted by JeteMc View Post
    It seems to me that when the corresponding value in column B is zero that the cell in column D should display "N/A", therefore I suggest the following for D6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    It makes sense that you are referencing B6 for "0" to display "N/A" in D6 but it does not seem to accept your formula as a function:

    Error.jpg

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,270

    Re: =MIN *Custom Message When Null*

    Here is the formula applied to cells D6:D7.
    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-13-2022
    Location
    Florida, USA
    MS-Off Ver
    365
    Posts
    11

    Re: =MIN *Custom Message When Null*

    Quote Originally Posted by JeteMc View Post
    Here is the formula applied to cells D6:D7.
    Let us know if you have any questions.
    THANK YOU SO MUCH! You solved both problems of the N/A and the date format.

    Will be continuing to develop this further, so expect another round of questions

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,270

    Re: =MIN *Custom Message When Null*

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Check range of cells in column if not null/empty then restrict save and show message
    By gmalpani in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2020, 09:25 AM
  2. Replies: 11
    Last Post: 04-04-2019, 10:52 PM
  3. Custom Message Box VBA
    By Wamwame in forum Excel General
    Replies: 4
    Last Post: 06-14-2016, 09:54 AM
  4. replace VBA run-time error message with custom message
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-14-2006, 11:05 AM
  5. Replace Excel Message w/Custom Message
    By Kevin R in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2006, 11:15 AM
  6. [SOLVED] Can a function return a Null (blank ) value? Maybe a custom functi
    By colin_e in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2006, 10:40 AM
  7. Error/Warning Message for Null Cells
    By Spyder in forum Excel General
    Replies: 2
    Last Post: 03-23-2005, 12:06 PM

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