Excel 2000 - copying/pasting formula for validation purposes
Is it possible to copy a formula from a cell and paste it as text?
I am writing spreadsheet validation documents which must show the formula used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15). Thanks |
Excel 2000 - copying/pasting formula for validation purposes
Hi
Place a single quote ' in the cells, before you paste. That will force the entry to be text. -- Regards Roger Govier "Silena K-K" wrote in message ... Is it possible to copy a formula from a cell and paste it as text? I am writing spreadsheet validation documents which must show the formula used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15). Thanks |
Excel 2000 - copying/pasting formula for validation purposes
Hi Roger
It worked if I placed the single quote ' at the beginning of the formula after I had pasted it into another cell but not before. Do you know if I can add the single quote ' automatically - some type of add/replace function(??) at the beginning of the formula? Regards, Silena "Roger Govier" wrote: Hi Place a single quote ' in the cells, before you paste. That will force the entry to be text. -- Regards Roger Govier "Silena K-K" wrote in message ... Is it possible to copy a formula from a cell and paste it as text? I am writing spreadsheet validation documents which must show the formula used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15). Thanks |
Excel 2000 - copying/pasting formula for validation purposes
Rather than copy the formula you could use a UDF to show the formula.
Function ShowFormula(Cell) Application.Volatile ShowFormula = "No Formula" If Cell.HasFormula Then ShowFormula = Mid(Cell.Formula, 2) End Function Usage is: =ShowFormula(cellref) will return SUM(B8:B15) Gord Dibben MS Excel MVP On Thu, 29 Nov 2007 13:11:00 -0800, Silena K-K wrote: Is it possible to copy a formula from a cell and paste it as text? I am writing spreadsheet validation documents which must show the formula used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15). Thanks |
Excel 2000 - copying/pasting formula for validation purposes
Hi
Sorry about that. The following short bit of code will create a list of the formulae in column A, in column B in text form. The code is set to run from row 1 to 50, change to suit your requirement. Also, alter the column letters to suit. Sub changeformulatotext() Dim i As Long For i = 1 To 50 Cells(i, "B") = "'" & Cells(i, "A").Formula Next End Sub Copy the above code. press alt+F11 to bring up the Visual Basic Editor InsertModule In the white pane that appears, paste the code you have copied Alt+F11 to return to Excel Alt+F8 to bring up macros, highlight the macro name and click Run -- Regards Roger Govier "Silena K-K" wrote in message ... Hi Roger It worked if I placed the single quote ' at the beginning of the formula after I had pasted it into another cell but not before. Do you know if I can add the single quote ' automatically - some type of add/replace function(??) at the beginning of the formula? Regards, Silena "Roger Govier" wrote: Hi Place a single quote ' in the cells, before you paste. That will force the entry to be text. -- Regards Roger Govier "Silena K-K" wrote in message ... Is it possible to copy a formula from a cell and paste it as text? I am writing spreadsheet validation documents which must show the formula used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15). Thanks |
Excel 2000 - copying/pasting formula for validation purposes
Hi Roger
Thank you and have a wonderful Christmas/NY. Regards, Silena "Roger Govier" wrote: Hi Sorry about that. The following short bit of code will create a list of the formulae in column A, in column B in text form. The code is set to run from row 1 to 50, change to suit your requirement. Also, alter the column letters to suit. Sub changeformulatotext() Dim i As Long For i = 1 To 50 Cells(i, "B") = "'" & Cells(i, "A").Formula Next End Sub Copy the above code. press alt+F11 to bring up the Visual Basic Editor InsertModule In the white pane that appears, paste the code you have copied Alt+F11 to return to Excel Alt+F8 to bring up macros, highlight the macro name and click Run -- Regards Roger Govier "Silena K-K" wrote in message ... Hi Roger It worked if I placed the single quote ' at the beginning of the formula after I had pasted it into another cell but not before. Do you know if I can add the single quote ' automatically - some type of add/replace function(??) at the beginning of the formula? Regards, Silena "Roger Govier" wrote: Hi Place a single quote ' in the cells, before you paste. That will force the entry to be text. -- Regards Roger Govier "Silena K-K" wrote in message ... Is it possible to copy a formula from a cell and paste it as text? I am writing spreadsheet validation documents which must show the formula used in each calculated cell eg: =SUM(B8:B15) is written as SUM(B8:B15). Thanks |
All times are GMT +1. The time now is 11:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com