Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: If No Data Leave It Blank (cell Contains Formula)

  1. #1
    Join Date
    30th March 2007
    Posts
    21

    If No Data Leave It Blank (cell Contains Formula)

    Hi,

    A1 has a date of 03/10/07
    B2 has formula =A1 + 7
    I want to copy that formula down the column B2 but if
    B3 is empty I get 01/07/1900
    How Do I make so that if there's no date entered in
    A column I get blank cells but still retain formulas,
    I've been running into this issue with other formulas but I couldn't find any solution searching this forum,

    How do you call this type of problem ?

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    27th January 2003
    Location
    Montana
    Posts
    204

    Re: If No Data Leave It Blank (cell Contains Formula)

    This Formula would work
    =IF(A1="","",A1+7)
    I would prefer the Before Double_Click instead
    VB:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
        If Target.Column <> 2 Then Exit Sub 
        If Target.Offset(0, -1) = "" Then 
            Cancel = True 
            Exit Sub 
        Else: Target = Target.Offset(0, -1) + 7 
        End If 
        Cancel = True 
    End Sub 
    
    
    Or you can use a WorkSheet Change event
    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If Target.Count > 1 Then Exit Sub 
        If Target.Column <> 1 Then Exit Sub 
        If Application.WorksheetFunction.IsNumber(Target) = True Then Target.Offset(0, 1) = Target + 7 
    End Sub 
    
    
    lenze
    Last edited by royUK; April 1st, 2007 at 01:43.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: If No Data Leave It Blank (cell Contains Formula)

    I don't understand. Is this manual copying or in VBA?

    If manual, then does B2 have to still refer to A1 or to A2? And how can B3 be empty if you are copying the formula down into it? Perhaps you could attach a sample workbook with the problem.

    Welcome to the forum.
    Last edited by ByTheCringe2; March 31st, 2007 at 04:24.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th March 2007
    Posts
    21

    Re: If No Data Leave It Blank (cell Contains Formula)

    First of all thanks for a warm welcome!

    this solves my problem =If(A1="","",A1+7)
    Since I'm new to vba when I get home I'll try to figure out where to enter the other two codes and test how it works,
    attached is test spreadsheet in sheet1 is my problem in sheet2 problem is solved using above formula,
    I just need to figure out how to incorporate above in my other formulas,

    Thanks!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    27th January 2003
    Location
    Montana
    Posts
    204

    Re: If No Data Leave It Blank (cell Contains Formula)

    Since I'm new to vba when I get home I'll try to figure out where to enter the other two codes and test how it works
    Both codes would go in the WorkSheet module. Right Click on sheet tab and choose "View Code"

    lenze

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,906

    Re: If No Data Leave It Blank (cell Contains Formula)

    lenze, please don't use Code tags for formulas
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  7. #7
    Join Date
    30th March 2007
    Posts
    21

    Re: If No Data Leave It Blank (cell Contains Formula)

    Thanks lenze!

    you saved me a lot of time, I didn't check my email so I didn't see your reply and I was trying everything but your tip

    I tried your Double_Click but that only forbids double click of selected cell and changing value, second one WorkSheet Change event I couldn't figure out what it does
    anyway, your first suggestion works perfect =If(A1="","",A1+7) with that I'm able to copy formula down the column without 01/07/1900 appearing in every copied cell,

    I was able to implement your fix into my other formula = TODAY()-A2 and it works perfect again =IF(A3="","",TODAY()-A3)

    but I can't implement into this
    =IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times")

    any idea guys?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: If No Data Leave It Blank (cell Contains Formula)

    Try:

    =IF((B2="","",IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times"))

    EDIT: Contains an error, see correction below.
    Last edited by ByTheCringe2; April 1st, 2007 at 05:32.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    30th March 2007
    Posts
    21

    Re: If No Data Leave It Blank (cell Contains Formula)

    I tried and I got
    "The formula you typed contains an error"
    for information about fixing common.......

    B2 contains date 03/31/2007 and is getting that date from this formula =A2 + 30
    Last edited by JohnnyBeGood; April 1st, 2007 at 05:18.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: If No Data Leave It Blank (cell Contains Formula)

    Sorry, try:

    =IF(B2="","",IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times"))

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. Clear Value Cell & Leave Formula Cells
    By ABTAX in forum EXCEL HELP
    Replies: 5
    Last Post: December 20th, 2007, 21:23
  2. Replies: 7
    Last Post: May 22nd, 2007, 04:16
  3. If Tab Exists Put Cell, If Not, Leave Blank
    By nelacat in forum EXCEL HELP
    Replies: 18
    Last Post: February 26th, 2007, 11:30
  4. If Cell has Inactive Formula Leave Blank
    By Upside in forum EXCEL HELP
    Replies: 2
    Last Post: January 15th, 2007, 13:01
  5. Formulas: Leave a cell blank using an if condition
    By gwhitley in forum EXCEL HELP
    Replies: 10
    Last Post: July 17th, 2003, 23:55

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno