How to ignore Blank cells in formula and not to show any data

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
96
Office Version
  1. 2021
Platform
  1. Windows
I Have a workbook which is collecting data from another workbook "Oct" through simple formula (='D:\Desktop\Oct.xlsm'!Table1[@Column6])
my probelm is that when a cell is blank in the source workbook I get "0" in the cells of the collecting sheet and if there is a date cell empty in the sorurce I get "00.01.00"

please let me know how to make the formula display the blank cells as blank and only to show the cells that contain data
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try something like:
Excel Formula:
=LET(result, your_formula, IF(result="","",result))
replace "your_formula" with the formula you are currently using.
 
Upvote 0
Solution
Try something like:
Excel Formula:
=LET(result, your_formula, IF(result="","",result))
replace "your_formula" with the formula you are currently using.
ok I tried it it's ok with text cells but when a cell has a date I see that date value changed to another date
how to fix that
 
Last edited by a moderator:
Upvote 0
result is just a variable name and can be whatever word or letters you want to use.
in this case result will hold whatever value the next part after the comma returns ie 'D:\Desktop\Oct.xlsm'!Table1[@Column6]
Excel Formula:
=LET(result, 'D:\Desktop\Oct.xlsm'!Table1[@Column6], IF(result="","",result))
without the let it would look like this:
Excel Formula:
=IF('D:\Desktop\Oct.xlsm'!Table1[@Column6]="", "", 'D:\Desktop\Oct.xlsm'!Table1[@Column6]))

The Let command format looks something like the below. It is 1 or more value pairs with the final item that is not a pair being the output:
Excel Formula:
=let(name1,name_value1,
     name2,name_value2,
     name3,name_value3,
     Final_Calculation_Or_Output)
 
Upvote 0

Forum statistics

Threads
1,226,113
Messages
6,189,048
Members
453,522
Latest member
Seeker2025

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top