I am planning to email an excel spreadsheet as a survey. Is there any way to hide the formula bar? I would prefer that the user not see my formulas when they click on a cell. I can hide the formula within the sheet, but can't find out if I can prevent the user from seeing the formula in the formula bar.
View 9 RepliesThis is a simple division function using 2 cells. Normally if the cell referring to the numerator of the division forumla is blank you will get the "divide by zero error" (#DIV/0!). I have this sheet where there is no formula in the cell content (cell E1) but if I enter numbers in cell B1 and D1 it calculates:
=(B1-D1)/B1
How is this formula hidden? Normally if I would want to hide #DIV/0! I would use an IF function but I would still see the formula and if fuction if I click on the cell. Whats odd the formula doesnt show when I click on the cell but once i enter data in the cells the forumla appears.
I have to hide the formula in the cell. nobody should see the formula in the cell without password.
View 1 Replies View RelatedI have a sheet with many different cells containing formulas. When the sheet has no data entered yet, every cell needs to be blank, including cells that have formulas for data.
I've realized that I can't use the "Show a zero in cells that have zero value" option because if a cell has data entered in it and the result is 0, I sometimes need to see that 0 in the cell. Some cells I need to see the 0 result and some cells I need to see a blank cell if the result is 0.
I just can't figure out how to edit the formulas so they do what I need.
cell P11:
=IF(J11="","",INT((J11-10)/2))
If no data is entered in cell J11, then cell P11 should be blank. But if data is entered in cell J11 and the result in cell P11 is 0, then cell P11 should be blank.
cell BS27...........................
In this file i want the red coloured cells not to show zero (0) or anything unless anything else is entered in the cells above.In other words I want to hide the zero if nothing is entered above.
View 5 Replies View RelatedIn Spreadsheet A Cell H8 is entered as Item Description.
In Spreadsheet B Cell H8 is also entered as Item Description, but if the Item Description in Spreadsheet A has been changed, I need Spreadsheet B to reflect that change, if not it should stay as Item Description.
On my 'Report' worksheet in cells I1 to Z1 the formula returns "" if there's no data in that month or "1" to "18" depending on which month it is. e.g. if it's a 12 month accounting period then 6 of the columns will have no data in them and will have "" in row 1, the other columns will return 1 - 12 in row 1; if it's a 15 month accounting period then 3 of the columns will have no data in them and "" in row 1 and the other columns will return 1 - 15.
The VBA code below is part of a longer macro on my 'Data' worksheet. The rest of the code works fine and I just wanted to add this bit to hide the blank columns on the 'Report' page.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculate
With Sheets("Report")
Dim i As Integer
If Intersect(Target, Range("I1:Z1")) Is Nothing Then
Else
For i = 9 To 26
[Code] ........
I have a worksheet in which one particular cell contains a certain formula. For security reasons, I wish to hide the FORMULA contained in the cell. However, the VALUE of the cell given by the above formula should always remain displayed for the user.
I want this to be done only for that particular cell. I tried hiding the formula bar,but it hides it for all cells.
I am using the formula =IF(ISNUMBER(SEARCH("v",A2)),"OK", "Not OK") to say if cell a2 contains the letter v, then return ok. It would be really helpfull if I could say : if cell a2 contains the letter "v" or the letter "w" or the letter "x", then return ok.
View 2 Replies View RelatedI have a formulae set up on a sheet to calculate the average of the row. When there are no values in the row, it comes up with # DIV/0! as you cannot divide by 0 which I know. Is there a way to not show this until values are entered?
View 3 Replies View RelatedI have a spreadsheet that has a AGE column. The function was put in the top cell and after listing pulled down to automatically get the right cell indicators in the formule. I have 1000 rows on the spreadsheet and i should for see adleast a other 1000. The issue is as follow or better the question, how do i hide de #VALUE in de cells were there is no DOB yet? Since i put in the empthy rows in the DOB column / / i do get the #VALUE there where the DOB will come. Hoe can i solve this issue and make the additional rows still availeble for futher usage.
View 2 Replies View RelatedI am creating a worksheet for my buisness and I have input date of birth and have done a formula to work out ages but I dont know how to hide the formula and leave the cell blank so that when I create new information it automatically puts the age in.
View 1 Replies View RelatedI have formulas in cells B8:B365 that pull project names (in words) from a plugin using values specified in other cells (non-formula). If a project is inactive, the cell returns a 0.
I am looking for a macro that will hide the rows in this range where the B cells = 0. If there is no data at all, I want to leave the row as-is (unhidden). I also have 2 other specific sheets in the workbook that I'd like to include in the formula without having to repeat the macro 2 other times.
The one I am currently using just hides every row, regardless of if there is a title, a 0, or nothing. Here is the code (I don't know how to create those nifty little text boxes):
Excel 2003.
I have been struggling for an hour how to hide a number to text macro in a way that only macro result is displayed in a cell.
(I have one 2-3 years old xls, where I have managed with task, but now can't figure out how and how to unhide the macro
Can it be password protected somehow?
I have Cell C63 in Worksheet 1 which is set up to say either "YES" or "NO" depending on whether a value on a different worksheet exceeds a value in an adjacent cell or not (also on that worksheet). The formula I am using is simply:
=IF('4. NORMALITY ASSESSMENT'!AC55>'4. NORMALITY ASSESSMENT'!AA55,"YES", "NO")
Essentially I don't want anything displayed if there are no data in the range C4:C54 on Worksheet 1. I thought I need a nested IF function but I couldn't get this to work.
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2")) Is Nothing Then
If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then
Range("A3:A60").EntireRow.Hidden = True
Else
Range("A3:A60").EntireRow.Hidden = False
End If
End If
End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
I have an Excel workbook with multiple sheets. In one sheet, there are many columns that automatically get hidden based on cells values (=1) in another sheet as I type. I use this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" And Target.Value = 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = True
Else
If Target.Address = "$C$7" And Target.Value <> 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = False
End If
End If
End Sub
Recently I had to change the second sheet that instead of manually entered values, formula results appear in cells. And the above code doesn't work anymore, columns are always stay unhidden. How can I achieve what I want? I need columns get hidden if formula results =1.
I have a file having around 57 sheets.But here,I have taken a example.I want a file whose show me only summary sheet.But when,I click the cell no.C7 then the related sheet should be open i.e.FNDADRSCC,and all the sheets should be hide.I have little knowledge of HYPERLINK formula but not to the desired stage.
For more clarification, refer attached file.i.e. BOM Sheets
Desire result file have attached also i.e. Excel_formula
I want to create a macro that checks whether a specified range of columns is hidden, and
a) hide them if they are not hidden, or
b) unhide them if they are hidden,
i.e. toggle the Hide state.
What method can I use to query the hidden state of a selected range of columns and return a boolean value?
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View 2 Replies View RelatedIs it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies View RelatedIf A1 is also found below A1 (A2,A3,etc) then "HIDE" otherwise "DON'T HIDE"
I want to create a formula which finds out if a value exists below it in the same column. I could then use a filter to remove rows contain the word "HIDE" which will leave me with only rows with DON't HIDE. Then Column A will only contain unique values.How would I create a formula like this?
How can I hide the value in column H when there is no value in column E, but still be able to fill down the formula in column H as many rows as I want?
In other words, I don't want to see the totals in column H in rows where the items haven't been sold yet (col. E) or in rows where there is no other data at all. Items are not always sold in the order in which they were purchased but I still need the formula in column H to be intact.
I have a big formula with 6 IF functions and i whant to display 0 when formula returns #N/A.
I can't use IF(ISERROR()) because there are to many IF's functions
If I want to hide row a15 when cell value in k5=1 and also if cell value in k5=2 then hide row A16.
View 9 Replies View Relatedhow can i hide zero from Pt. i am using xl2003
View 9 Replies View Relatedis there a macro or bit of VBA code that can hide a worksheet tab when criteria in an if function is true???