Conditional Formatting - 'Invisible' typing before pressing 'Enter' - Any help, pls?

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125
Using MS Excel 2007 / 2007

I have cells which contain formula to copy data from equivalent cell on previous sheet, ie in February C26 sheet, it will show the data in January C26 sheet.

Consequently, I had a column of ‘0’ which I didn’t want showing, so used Conditional Formatting to set ‘If C26 = 0, then font colour same as background’ for the whole column

This works very well in that it hides the ‘0’s.

However, when data is entered into the ‘empty’ cells, ie those with the ‘0’s hidden as no data in previous month's sheet, below the already filled ones from the previous month, the characters typed can’t be seen until after ‘Enter’ has been pressed, 'invisible' typing.

I presume this is due to the Conditional Formatting and that any characters typed will be ‘considered’ as ‘0’ until Enter is pressed.

Not being able to see what is being typed into the cell is a bit of an issue, although I realise I could check the box at the top. Is there a way to overcome this ‘invisible’ entry of text, please?

As always, I’m very grateful for any help or advice
Many thanks
%
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Why not use a formula to 'hide' the 0?

=IF(Sheet2!C26=0, "", C26)
 

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125
Why not use a formula to 'hide' the 0?

=IF(Sheet2!C26=0, "", C26)

Hi Norie

I've just tried your suggestion but only got a '0' as a result, instead of the data [Name] from equivalent cell on previous month.

My tabs are labelled as 'Jan', 'Feb', 'Mar' etc - so I tried this formula, adapted from your suggestion (possibly incorrectly, so please correct me if that's the case)

I've put this " =IF(Feb!C24=0, "", C24) " into C24 on a new sheet, 'Sheet!'

There is data in Feb C24, so should be copying that across.

I need some help, please
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
The formula I posted assumed you were dealing with numerical data.

What type of data is in C24 on the 'Feb' sheet?

Does this work?

=IF(Feb!C24="", "", C24)
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello PurrSent

If I'm reading your original post correctly, you want whatever is in the same cell location from the previous month to be brought forward to the current month's sheet at that same location. I think the reason the formula of =IF(FEB!C26="","",C26) placed in the FEB sheet is NOT bringing the value from cell C26 of JAN forward, is that this formula as it is written, is looking at cell C26 of the current (or FEB) sheet. You need to specify which sheet you want the formula to gather the data from.

Try placing this in the FEB sheet.

Code:
[COLOR=black][FONT=Calibri]=IF(JAN!C26="","",JAN!C26)[/FONT][/COLOR]

I hope this is what you are looking for.

TotallyConfused
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
Hello PurrSent

After thinking about my post #5 , I realise it won't work if the cell in JAN had a value of zero because it would bring that zero forward. Try using the following formula in FEB cell C26. I think this should work regardless of whether JAN C26 is numeric, text, a zero value, or null.
Code:
[COLOR=black][FONT=Arial]=IF(JAN!C26=0,"",JAN!C26)[/FONT][/COLOR]
I hope this works.
TotallyConfused
 
Last edited:

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125

ADVERTISEMENT

Hi Totally Confused

Your second formula has worked well so I'm going to add it all to the worksheets now. I'm assuming it won't affect the Sorting of Date into ascending order, obviously taking with it the associated rows!!! I'll try it on one month first, just to check ;)

Thank you very much for helping me with this.

Also, thank you, Norie, for your help. Although it hasn't been the solution, the idea of using such a formula hadn't occured to me and I'd have needed help with doing that, anyway.

So, I'm very grateful to both of you. I'm so grateful to all you guys on this forum 'coz you give such expert help, and your time, to folks like myself who, without you all, wouldn't manage to achieve very much at all.

Again, many thanks to both of you, Norie and Totally Confused ;)
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
Hello PurrSent

You're very welcome. I'm glad you found a solution to your problem. In all fairness, I can't take much credit though, because as you noticed, all I did was to 'steal' the basic solution that Norie suggested and sort of fine tuned it. Thanks for the feedback. It is always encouraging to hear that something we have done has helped someone.

TotallyConfused
 
Last edited:

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
125
In all fairness, I can't take much credit though, because as you noticed, all I did was to 'steal' the basic solution that Norie suggested and sort of fine tuned it.

It's good of you to say this and I hope Norie will see this and appreciate it. Yes, Norie certainly was able to think from a different angle. So pleased it led to a solutionm :) Again, many thanks, Norie
Thanks for the feedback. It is always encouraging to hear that something we have done has helped someone.
I do like to give feedback, although sometimes I'll forget where I'd posted ;) I think feedback is important for several reasons. One of them is to let the people who've helped know they've been appreciated and also for others who have a problem to see the solution. It may also help some to have the confidence to post their issue too, when they see such good help and advice :)

So, again, thank you, both, very much for all your help :)

%
 

Watch MrExcel Video

Forum statistics

Threads
1,122,409
Messages
5,595,973
Members
414,035
Latest member
billbumkins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top