Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulas
I want to display formulas in only 2 columns of the trial balance worksheet,
and the rest of the worksheet to display the values in the proper format. I have tried displaying the values, but it changes the whole worksheet. Any suggestions? Thanks...Debbie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulas
I am not sure what you mean, do you mean you want to display the formulas as
text string in 2 columns but in all other columns having them displayed normally? One possible way, select the 2 columns you want as text strings, press Ctrl + h, in the find box put = in the replace box put ^=^ select replace all, close the replace dialogue box would look something like this ^=^SUM(B2:B7) 9 -- Regards, Peo Sjoblom "Debbie" wrote in message ... I want to display formulas in only 2 columns of the trial balance worksheet, and the rest of the worksheet to display the values in the proper format. I have tried displaying the values, but it changes the whole worksheet. Any suggestions? Thanks...Debbie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulas
Cannot be done without a bit of VBA.
Function ShowFormula(cell) Application.Volatile ShowFormula = "No Formula" If cell.HasFormula Then ShowFormula = cell.Formula End Function Usage is: =ShowFormula(cellref) as in =ShowFormula(A1) Copy/paste the UDF above into a General Module in your workbook. If not familiar with macros and VBA, visit David McRitchie's website on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the above code in there. Save the workbook and hit ALT + Q to return to your workbook. Gord Dibben Excel MVP On Sun, 14 Oct 2007 11:00:00 -0700, Debbie wrote: I want to display formulas in only 2 columns of the trial balance worksheet, and the rest of the worksheet to display the values in the proper format. I have tried displaying the values, but it changes the whole worksheet. Any suggestions? Thanks...Debbie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulas
Hi there, thanks alot for your response, I tried what you've suggested, and
it works, but if I have something in a column after that, and now it doesn't read the formula anymore. What I need to do is show columns E & G with what the formula is but all the other columns, before and after columns E & G to show the correct values in the correct format. Does this make more sense? Thanks alot, Debbie "Peo Sjoblom" wrote: I am not sure what you mean, do you mean you want to display the formulas as text string in 2 columns but in all other columns having them displayed normally? One possible way, select the 2 columns you want as text strings, press Ctrl + h, in the find box put = in the replace box put ^=^ select replace all, close the replace dialogue box would look something like this ^=^SUM(B2:B7) 9 -- Regards, Peo Sjoblom "Debbie" wrote in message ... I want to display formulas in only 2 columns of the trial balance worksheet, and the rest of the worksheet to display the values in the proper format. I have tried displaying the values, but it changes the whole worksheet. Any suggestions? Thanks...Debbie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulas
Have a look at my first response Debbie.
You will retain your original formulas but see them in the helper columns. Question..................why do you want to see the formulas and not the results? Gord Dibben MS Excel MVP On Sun, 14 Oct 2007 15:32:00 -0700, Debbie wrote: Hi there, thanks alot for your response, I tried what you've suggested, and it works, but if I have something in a column after that, and now it doesn't read the formula anymore. What I need to do is show columns E & G with what the formula is but all the other columns, before and after columns E & G to show the correct values in the correct format. Does this make more sense? Thanks alot, Debbie "Peo Sjoblom" wrote: I am not sure what you mean, do you mean you want to display the formulas as text string in 2 columns but in all other columns having them displayed normally? One possible way, select the 2 columns you want as text strings, press Ctrl + h, in the find box put = in the replace box put ^=^ select replace all, close the replace dialogue box would look something like this ^=^SUM(B2:B7) 9 -- Regards, Peo Sjoblom "Debbie" wrote in message ... I want to display formulas in only 2 columns of the trial balance worksheet, and the rest of the worksheet to display the values in the proper format. I have tried displaying the values, but it changes the whole worksheet. Any suggestions? Thanks...Debbie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulas
I tried that at work today, and it worked. That's great. I have 2 other
questions......if there is no formula, just a value, is there a way to show the value instead of saying "No Formula", also when there is nothing in the cell, is there a way to leave the cell blank, instead of saying "No Formula"? The reason behind this is for an accounting firm that is doing a trial balance. So they would like to show the amounts of CR & DR adjustments before the total columns. (they use to use Lotus123) Thanks alot for your help! -- Debbie "Gord Dibben" wrote: Have a look at my first response Debbie. You will retain your original formulas but see them in the helper columns. Question..................why do you want to see the formulas and not the results? Gord Dibben MS Excel MVP On Sun, 14 Oct 2007 15:32:00 -0700, Debbie wrote: Hi there, thanks alot for your response, I tried what you've suggested, and it works, but if I have something in a column after that, and now it doesn't read the formula anymore. What I need to do is show columns E & G with what the formula is but all the other columns, before and after columns E & G to show the correct values in the correct format. Does this make more sense? Thanks alot, Debbie "Peo Sjoblom" wrote: I am not sure what you mean, do you mean you want to display the formulas as text string in 2 columns but in all other columns having them displayed normally? One possible way, select the 2 columns you want as text strings, press Ctrl + h, in the find box put = in the replace box put ^=^ select replace all, close the replace dialogue box would look something like this ^=^SUM(B2:B7) 9 -- Regards, Peo Sjoblom "Debbie" wrote in message ... I want to display formulas in only 2 columns of the trial balance worksheet, and the rest of the worksheet to display the values in the proper format. I have tried displaying the values, but it changes the whole worksheet. Any suggestions? Thanks...Debbie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying formulas
Revised.
Function ShowFormula(cell) Application.Volatile ShowFormula = cell.Value If cell.HasFormula Then ShowFormula = cell.Formula End Function This on its own will leave a zero if the cell is blank. To overcome that that enter as follows same as for any other function. =IF(showformula(a1)=0,"",showformula(A1) Gord On Mon, 15 Oct 2007 12:46:02 -0700, Debbie wrote: I tried that at work today, and it worked. That's great. I have 2 other questions......if there is no formula, just a value, is there a way to show the value instead of saying "No Formula", also when there is nothing in the cell, is there a way to leave the cell blank, instead of saying "No Formula"? The reason behind this is for an accounting firm that is doing a trial balance. So they would like to show the amounts of CR & DR adjustments before the total columns. (they use to use Lotus123) Thanks alot for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy formulas displaying wrong value | Excel Discussion (Misc queries) | |||
Displaying Cell Formulas | Excel Discussion (Misc queries) | |||
Displaying Cell Formulas | Excel Discussion (Misc queries) | |||
displaying formulas | Excel Discussion (Misc queries) | |||
displaying results and not formulas | Excel Worksheet Functions |