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

[PurrSent]

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
%

 

[Norie]

Why not use a formula to 'hide' the 0?

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

 

[PurrSent]

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]

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]

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.

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

I hope this is what you are looking for.

TotallyConfused

 

[TotallyConfused]

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.

[COLOR=black][FONT=Arial]=IF(JAN!C26=0,"",JAN!C26)[/FONT][/COLOR]

I hope this works.
TotallyConfused

 

[PurrSent]

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]

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

 

[PurrSent]

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

%