+ Reply to Thread
Results 1 to 6 of 6

Show Value instead of Formula

  1. #1
    Registered User
    Join Date
    03-30-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    36

    Show Value instead of Formula

    Hello guys ,

    I would need help with my excel document, I have managed to do a dropdown list, in Cell A, and then if it shows a name, it coppies down the second column B2 from the source lists, (=IF(ISTEXT(A5),VLOOKUP(A5,list,2),"") and the same for the Cell C which is for an email adress (=IF(ISTEXT(A5),VLOOKUP(A5,list,2)
    everyting works just fine, but the problem is that in order to send email which is cell 3, instead of the email address it shows the formula and VBA in this instance dont work. as the VBA code is referenced to C column there it should catch an address and not a formula,

    Is there away to keep the formula, but hide it and show the result only ?

    Please find document attached



    Many thanks,

    Aivaras
    Attached Files Attached Files
    Last edited by Alcotraz; 03-31-2015 at 10:31 AM.

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Show Value instead of Formula

    I'm not sure if I follow correctly, but took a stab at it anyways. Is this what you are trying to accomplish?

    Please Login or Register  to view this content.
    Pasted into D5. Anytime you click on the email address, your email client will open a new message dialog box.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,006

    Re: Show Value instead of Formula

    @Alcotraz: Do you have < > in your second formula as it doesn't look right? Looks like the forum has corrupted it. Edit it and put spaces around the < > as it's currently illegible.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Show Value instead of Formula

    Likewise I'm not sure what you want. But... Maybe like this (pale blue cells C5:D24). No formula is visible.

    Done by Format/Format Cells/Protection/hidden and then protecting the sheet (there is no pasword, so just unprotect it).
    Attached Files Attached Files
    Glenn



  5. #5
    Registered User
    Join Date
    03-30-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    36

    Re: Show Value instead of Formula

    Hi guys thank you really much for responses and sorry if I wasnt that clear of what I am trying to achieve, but you see, when I use a drop down list , the column next to it recognizes the name and copies the name form the list, and the third column copies the email address, but it only shows on the cells name and address but in order for VBA code to work which is :

    Sub Reminder1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim strbody As String
    For Each cell In Range("K1:K100")
    strbody = strbody & cell.Value & vbNewLine
    Next
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Columns("D").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" And _
    LCase(Cells(cell.Row, "E").Value) = "yes" _
    And LCase(Cells(cell.Row, "E").Value) <> "send" Then
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
    .To = cell.Value
    .Subject = "Application Reminder for projcet"
    .HTMLBody = "Dear " & Cells(cell.Row, "C").Value & vbNewLine & vbNewLine & strbody
    'You can add files also like this
    '.Attachments.Add ("C:\test.txt")
    .Send 'Or use Display
    End With
    On Error GoTo 0
    Cells(cell.Row, "E").Value = "send"
    Set OutMail = Nothing
    End If
    Next cell
    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True

    End Sub

    For Each cell In Columns("D").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*"


    and
    .HTMLBody = "Dear " & Cells(cell.Row, "C").

    The formula needs to have an email address in Column D which now only shows as an email but the original value for it is =IF(ISTEXT(B5),VLOOKUP(B5,list,3),""))
    so the question is how do I keep the formula, which would copy email address to column D but it would keep email adress as a function in the cell.

    I hope you will understand it now.

  6. #6
    Registered User
    Join Date
    03-30-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    36

    Re: Show Value instead of Formula

    I have looked through the forums, and found that if you press F2 and F9 in the cell it runs from the formula to value, which is exactly what I am trying to achieve, instead I want to do it automatically after I sue the dropdown list, Copy paste values wont work as I need to keep the sheet for only a week, and different values will be entered so I dont want to get rid of the formula and than writing it again each time I use the dropdown list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2012, 03:49 PM
  2. [SOLVED] Formula result will not show when formula is copied to another cell
    By nabraham00 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-06-2012, 01:03 PM
  3. Date formula if positive show, if negative don't show.
    By Cyberpawz in forum Excel General
    Replies: 8
    Last Post: 03-14-2012, 07:49 AM
  4. Replies: 0
    Last Post: 09-04-2011, 09:14 AM
  5. if formula to show blank or show instead of displaying the running total
    By js1978 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2008, 04:38 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1