Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying formula as text (2000 SR-1)

    Can someone tell me if there is a function that returns a cell's formula as text?
    Ex. BN1789 contains "=-BN1612" and shows "(109.02)"
    I know how to copy and paste special values - Values to get the (109.02) into a new cell,
    and I know how to copy and paste special values - Formulas to get the formula itself into a new cell... but this displays the value referred to...
    I want to see "-BN1612" in the new cell...
    Is this possible??

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying formula as text (2000 SR-1)

    None built-in, but you can create one easily enough. If you add this to a VB module:

    <pre>Function DisplayFormula(rng As Range) As String
    DisplayFormula = rng.Cells(1).Formula
    End Function</pre>


    and then enter into a cell:
    <pre>=DisplayFormula(BN1789)</pre>


    you should get what you want.

    The only other way, I can think of off-hand, is to edit the cell BN1789 add an apostrophe in front of the equal sign, copy it to where you want the formula to display, then re-edit BN1789 to remove the apostrophe.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formula as text (2000 SR-1)

    That's perfect Steve!! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    I had used CStr() in a procedure before (thinking that when it went into the new cell it might think it was still a formula...) and that had worked....
    Ex. originSheet.Cells(rwNew, 2).Value = CStr(cdnSheet.Cells(rw, col - 3).Formula)
    but your function is great for doing this on the fly...

    I just wanted to know if there was some built in function that would do this that I didn't know about... There are tons that I don't know about!!

    Thanks a million!

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Copying formula as text (2000 SR-1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Trudi

    Although the answers you got are perfect, but one needs to ask why and what really are you trying to do. If for example you need to make sure that all the formulas are correct, pointing to the right cells/ranges, or have the right elements, in your case it needs a negative number, and things like that, you can go into your Tools Menu and under Options, choose the View tab and select Formulas under Window Options... Mind you this is a worksheet level option, so you will need to do the same for all worksheets conserned.

    This will turn all the formulas on and you can inspect them in their cells.

    Alternativly, you can change the '=' sign to some unique string, I use ZZZxxxZZZ, or even eliminate it totally, and that will convert the formula into a string. You see Excel knows it a formula by the presence of the equal sign in the begining.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formula as text (2000 SR-1)

    Thanks Wassim...

    I can understand your curiosity about my intention for this... The thing is that this workbook was created yearsssss ago... and there have been many people making many changes through the years (Not developers either!... )...The workbook is what my company calls the DIR (Daily Interest Report)... The company I work for is a corporate brokerage so all of our inventory is financial products... The workbook is supposed to balance and allocate all interest bearing products by revenue and expense...

    There are over 20 sheets and over 1800 named ranges... The sheet has all kinds of tables in funny places... I go bug-eyed trying to figure this thing out... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    The previous allocations were done by inventory (account number)... My boss asked me to change the allocations to split by Dept and Product.... I managed to do that quite quickly with a few normalized tables and a few queries in Access... but then the boss said... "Now all of the numbers have to be tied in to the old way"... Apparently there are 4 entries to be booked for each... 2 will be the old way and 2 the new way... Anyway... I've already found sooooo many errors... I was trying to figure out where all these numbers were coming from and going to on the sheets and I decided I needed to see the values and the formulas (references) to be able to follow it back... I thought this would be the quickest way...

    I appreciate the new information!... I didn't know that option in the Tools menu... I am by no means an Excel expert... I'm a Programmer Analyst... I'm a Database Developer... lol... It goes against my nature to leave this mess as is... I'd so love to scap this spreadsheet and do it right... BUT it's close to year end and there's just no chance I can do it now...

    My co-workers (and boss) must hear me say "I HATE this spreadsheet!" 10 times a day at least... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formula as text (2000 SR-1)

    A shortcut to see all the formulas is to use [Ctrl] ` (grave accent) (That's the key usually to the left of the "1" key, and has the two marks ` and ~.) This key combination automatically switches to formula view - and back. The best thing about this is you don't have to take your eyes off the cell in question.

    Also, I rebuilt a 15 page spreadsheet from scratch once to clear up some of the problems you mention. Is that a possibility for you? (You could mumble some buzz words like "efficiency" and "smaller file size" in your boss's hearing.)

    Errol

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formula as text (2000 SR-1)

    Thanks Errolv! I love learning new shortcuts like that... I'm sure I'll use it... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    As for rebuilding it... I've already spoken to my boss about it... She's onboard with doing it but says we have to wait until the new business year... There's an incredible amount of work to be done before the end of October...

    This thing is insane... There are text files imported into and stripped in an Access database in the morning... Then there's another database that imports the data to do with this particular process every day... Then the database does its work and exports 8 tables as spreadsheets... Then someone actually manually copies and pastes the data from each of those .xls files into 8 separate sheets in this workbook... The data is used throughout this workbook but the main purpose (as I've now identified) is to create another table on yet another sheet, and export it to yet another Access database to use to print journals (reports) for booking... It blows my mind at how incredibly ridiculous this thing is...

    I've talked to my boss about it and she understands that it's not a good way to do things... She says the reason it ended up this way is because different people have created different parts of the thing as needed... This is a perfect example why process we need to analyse the entire department's processes and do some serious normalizing... I am the first developer that she's ever hired for the department... It's always been non technical people creating this stuff...

    I think I'm going to have my work cut out for me here but I'm not complaining... I love a challenge!! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Copying formula as text (2000 SR-1)

    Hi Trudi,

    As an alternative to copying the formulae as text, you might like to convert them to comments attached to the cells themselves. Then, you'll be able to see each cell's formula anytime the mouse hovers over it, or by turnimg on the 'Comment & Indicator' option in Tools|Options|View.

    The following macro adds the formulae to the comments for each selected cell, or even the whole worksheet, and displays the comments in an appropriately-sized box. You may need to adjust the parameters for positioning the comments next to their cells - which only makes a difference when the 'Comment & Indicator' option in Tools|Options|View is checked.

    Cheers

    Sub AddFormulasToComments()
    Application.ScreenUpdating = False
    'skip over errors caused by trying to delete comments in cells with no comments
    On Error Resume Next
    'If the whole worksheet is selected, limit action to the used range.
    If Selection.Address = Cells.Address Then
    Set CommentRange = Range(ActiveSheet.UsedRange.Address)
    Else
    Set CommentRange = Range(Selection.Address)
    End If
    'If the cell contains a formula, turn it into a comment.
    For Each TargetCell In CommentRange
    With TargetCell
    'check whether the cell has a formula
    If Left(.Formula, 1) = "=" Then
    'delete any existing comment
    .Comment.Delete
    'add a new comment
    .AddComment
    'copy the formula into the comment box
    Comment.Text Text:=.Formula
    With .Comment.Shape
    'automatically resizes the comment
    .TextFrame.AutoSize = True
    'position the comment adjacent to its cell
    .IncrementLeft -11.25
    .IncrementTop 8.25
    End With
    End If
    End With
    Next
    Application.ScreenUpdating = True
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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