Show only cells that are not 0

[calgaryguy]

I have the following worksheet that counts values in a different worksheet. I would like to only show the code to the left, the number and the empty space to the right when the number is not 0. Sometimes it may be negative. I would like to also have those displayed. Any ideas on how to do this? I am also using conditional formatting where if the number is greater than 0, it turns yellow and blue if it is less than 0. Thanks again for your help.

Ken

06.10.21 - 06.11.03 Updated.xls
	G	H	I	J	K	L	M	N	O
1	Monday			Tuesday			Wednesday
2	23-Oct			24-Oct			25-Oct
3	PH1	4		PH1	3		PH1	2
4	PF1	0		PF1	0		PF1	0
5	PO1	0		PO1	0		PO1	0
6	PH2	0		PH2	0		PH2	0
7	TX2	0		TX2	1		TX2	1
Holes Worksheet

 

[Aladin Akyurek]

Select the cells of interest.

Activate Format|Cells >General >Custom

Enter for Type:

[=0]"";General

 

[calgaryguy]

OK, that got rid of the zeros but I would also like to get rid of the code in the cell to the left and blank cell to the right.

Maybe I should go at this a different way. What I would like to end up with is a list of all the code where the count is not zero for each day. Basically, each code represents a position that a number of people could work. I know how many people I need so I am subtracting that from the number I have scheduled. That gives me the number that I still need or if I have too many, it will give me a negative number. I want to have a list that I can work from where I know I need so many people for such and such position. Right now, I can print a week at a time but because we have so many different positions, it spans over 3 pages. A lot of the time, I just end up having a bunch of zeros. If I don't need to fill a particular position, I don't want to see it on my worksheet. Any ideas? Thanks.

Ken

 

[calgaryguy]

Just thought I would see if anyone has any ideas on how to do this. See my example at the top. Thanks.

Ken

 

[Airfix9]

My only suggestion is to stage it through an intermediate sheet and rank the results. Then, for each day, use the ranks as lookups that bring in only the data that you want.

Does that make sense?

 

[onlyadrafter]

Hello,

Why not have CONDITONAL FORMATTING, to check the cell to the right if it is 0 have the text the same as the background?

 

[Airfix9]

I think he wants them all to shift up one if the value is 0.

 

[onlyadrafter]

Hello,

Then perhaps, use filter, or sort.

 

[calgaryguy]

Airfix9, can you tell me more about using an intermediate sheet? The intermediate sheet could probably be what I have already. I'm just not sure how to rank and then use that as a lookup in a new sheet. I'm pretty much self taught when it comes to Excel so I appreciate all the pointers.

Ken

 

[Airfix9]

Hi calgaryguy,

Sorry, been on a course for a week (VBA - woohoo!). Do you still need help with this?

You've pretty much guessed the answer - use rank (I use a process that I call rank and re-rank to get unique lookup numbers). The intermediate sheet is pretty much your front-sheet, the new front-sheet gets rid of the gaps.

If you still need help, just holler.