Solved

Excel Formula Formatting Help

Posted on 2013-01-09
9
333 Views
Last Modified: 2013-01-21
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
Comment
Question by:vegas86
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 70

Expert Comment

by:KCTS
ID: 38761063
can you post an example so we can see exactly what the issue is ?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38761130
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
 

Author Comment

by:vegas86
ID: 38761225
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 70

Expert Comment

by:KCTS
ID: 38761249
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 38766734
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
 

Author Comment

by:vegas86
ID: 38784677
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38801110
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
 

Author Closing Comment

by:vegas86
ID: 38803474
A+++++++++ Result!!

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

THANK YOU!!!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question