SOLVED

Hide a formula

New Contributor

I have a column which contains a formula

=A1&"California",&B1

I want to hide the formula. When I do that using protection tab, I was able to hide the formula but my custom text is still visible. Any way I can hide all of the contents in a formula cell?

7 Replies

@GeeJay 

You wrote:

I want to hide the formula. When I do that using protection tab, I was able to hide the formula but my custom text is still visible. Any way I can hide all of the contents in a formula cell?

 

I can think of a couple of ways. First, though, if you don't mind, I'd kind of want to know why? Presumably it's serving a purpose of some kind...i.e., the resulting custom text is being used somewhere, somehow. Right?

 

Anyway, here are several fairly easy ways to hide all the contents:

 

  1. put the cell, formula and all, in a column of its own, and hide the column -- you can still refer to it and use it in whatever way you are using it.
  2. put the cell, formula and all, in a  row of its own, and hid the row -- you can still refer to it...
  3. put the cell, formula and all, on a different tab and hide the tab  -- you can still refer to it ...
  4. change the color of the text to the color of the background -- less effective, and depending on who else is using the workbook (from whom apparently you want it to be hidden) still readily changed to be visible 

 

Thanks! I am sorry if I was not super clear in my original post. I want to keep the column, I just don’t want to display the formula (everything that appears after the = sign). I will be sending out the spreadsheet as a template so when people add values in A and B column, they would get a result in column C ( column c contains the formula).

@GeeJay 

The first time you wrote:

I want to hide the formula. When I do that using protection tab, I was able to hide the formula but my custom text is still visible. Any way I can hide all of the contents in a formula cell?

 

Meaning, if I take the words for what they, you know, mean...you were able to hide the formula. But you wanted to hide everything.  It was quite clear.

 

The second time around you wrote this:

I am sorry if I was not super clear in my original post. I want to keep the column, I just don’t want to display the formula (everything that appears after the = sign). I will be sending out the spreadsheet as a template so when people add values in A and B column, they would get a result in column C ( column c contains the formula).

 

So you want them to be able to see those results in column C--is that right? And you don't want them to see the formula, right?

 

But isn't that exactly what you were getting in the first place, before your first post.... Seeing the results but not the formula?

 

It's possible that I'm missing something here, but I need to ask you to clarify your clarification.

Thanks for replying. Yes I want others to be able to see the results in C but not the formula. When I tried to hide using the protection tab, I was able to hide everything after the = sign EXCEPT the word “California”. This word was hard coded in the formula coz I want to add this text in the result.

@GeeJay 

 

How about this, then, as a solution: Use a cell off to the side (or on a different tab) to do the concatenation involved in the formula =A1&"California",&B1.

 

Let's say it's cell Z1

Then in C1, protected, just have the entry =Z1

And column Z can be hidden from sight altogether, just by being way off to the side, or highlight the column and select "Hide" 

 

 

best response confirmed by Hans Vogelaar (MVP)
Solution

@GeeJay 

 

Oh! I just realized the simplest solution. So long as I'm correct that the original situation was that the formula, per se, is hidden and it's only the hardcoded "California" that is appearing.

 

Just make the result visible only after A1 and B1 have content in them, using an IF condition.

=IF(AND(A1="",B1=""),"",A1&"California"&B1)

This will keep C1 blank until entries are made in A1 and B1.

Depending on what goes into A1 and B1, assuming they're text fields of some kind, you might want to put spaces before and after the word "California"

=IF(AND(A1="",B1=""),"",A1&" California "&B1)

 

Oh that’s exactly what I wanted. It worked seamlessly. Thanks a lot! Really appreciate it.

Thanks