+ Reply to Thread
Results 1 to 26 of 26

Show cell as empty if referenced one is empty

  1. 02-26-2024, 04:53 PM #1
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Show cell as empty if referenced one is empty

    Hi,

    Re the worksheet (in the image), I have the following formula in B4, which works perfectly - when there's data.

    Please Login or Register  to view this content.
    However, when there isn't data in A4 and/or B4 I would like the count column (C) to also appear empty. I have tried adding various if(A4="","", type things into it, in various places, but nothing seems to work - they all mess up the LET formula. Is there something I can do to achieve this?

    Thanks in advance!

    Olly.

    Also posted on MREXCEL.COM: https://www.mrexcel.com/board/thread...empty.1254622/IMG_7258.jpeg
    Last edited by ollyhughes1982; 02-26-2024 at 04:56 PM.
    Reply With QuoteRegister To Reply
  2. 02-26-2024, 05:52 PM #2
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    You could try.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 02-26-2024, 05:58 PM #3
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    Thank you. I will give this a try!
    Reply With QuoteRegister To Reply
  4. 02-26-2024, 06:10 PM #4
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    Hi. I tried it and I get this strange errorAttachment 860968
    Reply With QuoteRegister To Reply
  5. 02-26-2024, 06:12 PM #5
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    Well, could you attach a small sample file?
    Reply With QuoteRegister To Reply
  6. 02-26-2024, 06:13 PM #6
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    I can try and create one. the file is massive, almost 200mb. I'll have a try now
    Reply With QuoteRegister To Reply
  7. 02-26-2024, 06:27 PM #7
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    Hi. File should now be attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-26-2024, 07:10 PM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,467

    Re: Show cell as empty if referenced one is empty

    Maybe this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  9. 02-26-2024, 07:22 PM #9
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    Column A is dependent of the fórmula in column B (cell B4), so there is nothing to do there.
    The formula in B4 already have a filter for empty values in 'All Completed Runs'!E4:E357<>"" what leave space to question if the column 'All Completed Runs'!D:D will ever be empty if column 'All Completed Runs'!E:E is filled?

    Well, we could add another condition for the filter, like 'All Completed Runs'!D4:D357<>"", just to be sure that the formula will never present an empty value.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 02-26-2024, 07:27 PM #10
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    Thanks, but formula still gives me the same error. Not sure why. Attachment 860971
    Reply With QuoteRegister To Reply
  11. 02-26-2024, 07:31 PM #11
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    I'm not seeing the error images, the forum is irregular about showing images. May be if you send it inside the worksheet.
    There was no error in the file that I opened.
    Reply With QuoteRegister To Reply
  12. 02-26-2024, 07:35 PM #12
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    The error appears when I enter your formula above, I've included the screenshot within the new file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 02-26-2024, 07:49 PM #13
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    Ohhh, that, just put all in a row, I separated to make it easier to read.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 02-26-2024, 08:02 PM #14
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    I already did that, it still gives the same error
    Reply With QuoteRegister To Reply
  15. 02-26-2024, 08:05 PM #15
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,467

    Re: Show cell as empty if referenced one is empty

    Did you see post #8?

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by TMS; 02-26-2024 at 08:08 PM.
    Reply With QuoteRegister To Reply
  16. 02-26-2024, 08:10 PM #16
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,467

    Re: Show cell as empty if referenced one is empty

    The formula in posts #11 and #13 has a semi-colon in it. Should probably be a comma.
    Reply With QuoteRegister To Reply
  17. 02-26-2024, 08:14 PM #17
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    Apologies, I missed that. It doesn't seem to work either, it is putting "no data" in B4 and C4 "0". What I want is for B4 to appear empty and C4 the same.Screenshot 2024-02-27 at 00.12.56.png
    Reply With QuoteRegister To Reply
  18. 02-26-2024, 08:22 PM #18
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    Ah, is that a regional issue, using semi-colons rather than commas in some countries? Thanks
    Reply With QuoteRegister To Reply
  19. 02-26-2024, 08:28 PM #19
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    Quote Originally Posted by TMS View Post
    The formula in posts #11 and #13 has a semi-colon in it. Should probably be a comma.
    My God, this thing haunting me... ( ; )
    Reply With QuoteRegister To Reply
  20. 02-26-2024, 08:30 PM #20
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,467

    Re: Show cell as empty if referenced one is empty

    You can't put a null value in B4, which is what your IFERROR does, because that's what the COUNTIF is counting (I think). So you need it to put something there that the COUNTIF won't find. You could use Conditional Formatting to hide the "no data" (white font and background) and a Custom Format to hide the zero in C4.
    Reply With QuoteRegister To Reply
  21. 02-26-2024, 08:30 PM #21
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    It now works, after replacing the ";" with ","

    I think this is a regional setting. Thanks!

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  22. 02-26-2024, 08:31 PM #22
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,467

    Re: Show cell as empty if referenced one is empty

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  23. 02-26-2024, 08:32 PM #23
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    The formula should not allow empty space, therefor no "no data".
    Reply With QuoteRegister To Reply
  24. 02-26-2024, 08:35 PM #24
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Show cell as empty if referenced one is empty

    Sorry for the ";".
    Tks TMS!
    Reply With QuoteRegister To Reply
  25. 02-26-2024, 08:36 PM #25
    ollyhughes1982
    ollyhughes1982 is offline
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: Show cell as empty if referenced one is empty

    Yes, this is now solved. Thanks all!
    Reply With QuoteRegister To Reply
  26. 02-26-2024, 08:40 PM #26
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,467

    Re: Show cell as empty if referenced one is empty

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to set referenced cell to empty instead of zero.
    By paata01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2023, 09:36 PM
  2. [SOLVED] Not empty cells to adopt 'interior.color' of referenced cell
    By sirruckus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-01-2021, 09:24 AM
  3. [SOLVED] Check if referenced cell is empty on a specific date
    By streakotter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-11-2018, 07:22 AM
  4. Show cell empty if result is an error due to empty source
    By jackmcguigan1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2016, 09:54 AM
  5. [SOLVED] If cell is empty = show nothing
    By mack4n in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-29-2015, 02:29 AM
  6. [SOLVED] excel 2010 empty a cell according to other cells (without Deleting them just show empty)
    By pavlos_x in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2015, 01:46 PM
  7. Reference cell is empty but when referenced gives me value of 12:00 am
    By nhopkins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-26-2013, 05:02 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1