?
Solved

Excel Formula Formatting Help

Posted on 2013-01-09
9
Medium Priority
?
336 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 93

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

743 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