Copying formula as text (2000 SR-1)

Topic

New Reply

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??

Reply | Quote

Replies

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

Function DisplayFormula(rng As Range) As String
    DisplayFormula = rng.Cells(1).Formula
End Function

and then enter into a cell:

=DisplayFormula(BN1789)

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

Reply | Quote

That’s perfect Steve!!

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!

Reply | Quote

That’s perfect Steve!!

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!

Reply | Quote

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

Function DisplayFormula(rng As Range) As String
    DisplayFormula = rng.Cells(1).Formula
End Function

and then enter into a cell:

=DisplayFormula(BN1789)

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

Reply | Quote

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

Reply | Quote

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…

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…

Reply | Quote

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

Reply | Quote

Thanks Errolv! I love learning new shortcuts like that… I’m sure I’ll use it…

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!!

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

Thanks Errolv! I love learning new shortcuts like that… I’m sure I’ll use it…

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!!

Reply | Quote

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

Reply | Quote

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…

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…

Reply | Quote

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

Reply | Quote