Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    formula displays in cell rather than result

    I have a formula in a cell that doesn't resolve. It displays like text


    =IF(LOOKUP($B4,'Manual Entry Timeline'!$A:$A,'Manual Entry Timeline'!J:J)#"",LOOKUP($B4,'Manual Entry Timeline'!$A:$A,'Manual Entry Timeline'!J:J),"")


    Before I just had LOOKUP($B4,'Manual Entry Timeline'!$A:$A,'Manual Entry Timeline'!J:J) and it worked fine, but it was putting zeros in my cell when the source was empty. So I tried the IF to get rid of it. All that happens is I now see the formula in the cell. What's up with that?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Perhaps the cell has been formatted, as text, in which case when you edit the cell any formula will now appear as text.
    If so, change the cell number format back to General (or whatever) rather than Text.

    Alternatively, you may have accidentally toggled the sheet view to "formula mode" by pressing Ctrl-` (the ` character is at top-left of your keyboard, under the [Esc] key, so press Ctrl-` again.

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    Thanks for the quick response. The cell is formatted as general. I've tried different formats but no change. And there are many formulas on the page, only this one displays and it only displays since I changed it to include the IF

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Re: "Before I just had LOOKUP($B4,'Manual Entry Timeline'!$A:$A,'Manual Entry Timeline'!J:J) and it worked fine"

    To stop it displaying zeros when the lookup is empty, just prefix your formula like this:
    =""&LOOKUP($B4,'Manual Entry Timeline'!$A:$A,'Manual Entry Timeline'!J:J)

    zeddy

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    Cool! That works.
    Now, how can I bring the formatting with the data in a lookup. I want the result to look just like the source. It has both text and cell formatting.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    You aren't going to be able to do this with a formula.

    Why can't you just format the cells that contain the formula with the same format as the source.

    To carry over the format you would need some VBA.

    Edit: How about using conditional formatting to apply the format you desire?
    Last edited by jrb; 2012-05-10 at 12:06. Reason: more info

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    The format of the source will change week to week so we can't manually format the display as it will change. The formatting is manual and up to the person doing the data entry so I can't cond format. There is also no way to tell what will be in the data so I can't see how to figure it out. We have to just be able to copy the formatting over as part of the automation. Can you point me in the direction of the VBA idea?

  8. #8
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    Do you have a small sample workbook to illustrate what you have in what cells and what you desire to what cells?

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    The first page is part of the display sheet. The second sheet is data entry. The timeline grey formatting is what I want to show up on the first sheet.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    How about this as worksheet code on Status Dashboard

    Right click tab >> view code >> paste on right side of screen

    Code:
    Private Sub Worksheet_Calculate()
        Dim LR As Long
        
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        
        With Sheets("Manual Entry Timeline")
            LR = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("B2:G" & LR).Copy
            Sheets("Status Dashboard").Range("C2:H" & LR).PasteSpecial xlPasteFormats
        End With
        
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
            .CutCopyMode = False
        End With
        
    End Sub

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi jrb

    Your code assumes everything is in the same row order on both sheets.
    If it was, you wouldn't need to use any lookups.
    So you could just copy all from the entry sheet and paste the lot.

    zeddy

  12. #12
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    Hi zeddy,

    Yes I did make an assumption, but after viewing the spreadsheet all rows from one tab matched up perfectly to the other tab. I guess we can get some more information from the OP. Do you have an idea for a possible solution?

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi jrb

    As always there are lots of ways of doing stuff in Excel.
    If the entries on the [Status Dashboard] were a subset, or in a different order etc, I suspect I would have a [Refresh] button that would trigger a VBA routine to quickly 'pull' all the data from the entry data sheet.
    To do this, I would probably use a temporary spare column to put a formula to find the matching row number of the Project, then use this to copy the relevant row data (including formats) etc etc

    zeddy

Posting Permissions

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