Solved

Excel Formula Formatting Help

Posted on 2013-01-09
9
323 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 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