Solved

Excel Formula Formatting Help

Posted on 2013-01-09
9
317 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 32

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

778 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