I want 2 cells to be blank if another is blank, otherwise those 2 cells can have the data in there.
Example: If there is a DATE in the "B" column, then I want "A", "C", and "E" columns to have their respected data. If there is no DATE in the "B" column, then I want "A", "C", and "E" columns to be blank.
Thanks in advance!
N528MW Revised.xlsx
Hi,
Since columns A,C or E don't derive their values as a result of a formula you can't unless you resort to a macro.
If you're happy to make the values invisible (but still present) then you could use conditional formatting to set the font colour to the same as the background colour.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
When you have a calculation, you can put it inside an IF statement so that it does one thing if something is true and another thing if that thing is false.
So in C10
=IF(ISNUMBER(B10), D9, "")
which reads "If B10 is a number, then enter the value in D9, if it's not, put a blank ("")
You can copy and paste that formula down the page.
Does that help?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Seems like a plain old IF statement could handle this based off the sample spreadsheet.
HOWEVER, your averages will not display a value if any of the cells the reference are blank. Would it be alright if instead your "blank" cells displayed 0 (they will display blank anyways and you could simply filter and delete/hide all 0's when finished). If not then your problem becomes slightly more complex (in my mind).
In either case, just add IF ISBLANK in front of your existing formula making the true statement blank or 0 (I recommend 0) and the false your previous formula (seems like you already knew this based on your title). So cell E10 for example would be =IF(ISBLANK($B10),"",F9) or =IF(ISBLANK($B10),0,$F9) and copy down.
Again, with this very simple solution, not using 0 will cause your averages to go all wonky.
...late edit.
That will teach me to look a little further. I saw that C9 was a hard coded value and hence posted as I did. Clearly this is an exception since all other values in C are formulae, and hence you can use the sort of things others have sine mentioned.
Thank you for all the responses... =IF(ISBLANK($B10),0,$F9) worked, and the "0" is perfect because I already had to hide zero's in my preferences.
Thanks again!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks