• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Excel Formula Formatting Help

Hello,

I have a range of columns that have been resized to be narrow. I have if statements in each cell so that if the date above matches another date, it displays someones name. However because I have the formula in every single cell, the names don't display because the next cell cuts it off because the blank formula is being recognised as a value. Is there a formula or macro that I can use so that if the formula returns a "" blank value the formula isn't recognised at all and it is shown as a real blank cell?

Any help on this will be greatly appreciated!!
0
vegas86
Asked:
vegas86
1 Solution
 
KCTSCommented:
can you post an example so we can see exactly what the issue is ?
0
 
Patrick MatthewsCommented:
Unfortunately, the answer is no.  Just about the only workaround I can think of is to modify the alignment settings in your cell format to enable "wrap text", although that may increase your row heights.
0
 
vegas86Author Commented:
I can't post the file as it is confidential, here is an image though if that helps sorry? I was thinking that maybe a macro could work? either one that looks for the last purple coloured cell in a row and then list cell contents from M15 next to it. Or one that looks a letter value and then deletes the formulas to the right ?
example.PNG
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
KCTSCommented:
I still can't see from the image exactly what you mean

As a general rule you could use a formula something along the lines of

=if (isblank(a3),"","Fred")

Which results in an empty cell if A3 is blank - otherwise it results in Fred
0
 
Rob HensonFinance AnalystCommented:
Even "" is not blank, it is a string of text with no characters.

The only recognition in Excel for blank is a completely empty cell, if the cell has "" as the result of a formula it is not blank because it contains a formula.

Yes, you could have a macro that looks at each row and populates the cell accordingly but more detail would be needed for writing this; a sample file would be good.

Thanks
Rob H
0
 
vegas86Author Commented:
Hi Rob,

Sorry for the delay in getting back to this. I have found this code:


Open in new window

Sub Demo()

Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select

End Sub

Which finds the last used cell. However I want it to check each row individually, find the last purple cell and then in the next blank cell put the contents of column A for each row.

Here is a skeleton of what I have without any data.
I really appreciate you looking at this for me!
ExampleFile.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Right-click on the sheet tab name
Select View code
Paste this code in the VBA window
Close the VBA window
Select column C
Copy it and paste it in the same location

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        For Each cel In Target
            Call Worksheet_Change(cel)
        Next cel
        Exit Sub
    End If
    If Target.Column = 3 And Target.Row > 4 Then
        Target.Offset(, 3).Resize(, 50).ClearContents
        Target.Offset(, 4 + Target - Range("F3")) = Target.Offset(, -2)
    End If
End Sub

Open in new window

0
 
vegas86Author Commented:
A+++++++++ Result!!

Thank you so much ssaqibh, it works perfectly. You always seem to come through for me! :)

THANK YOU!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now