Show only cells that are not 0

calgaryguy

Board Regular
Joined
Jul 10, 2006
Messages
116
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
GHIJKLMNO
1MondayTuesdayWednesday
223-Oct24-Oct25-Oct
3PH14PH13PH12
4PF10PF10PF10
5PO10PO10PO10
6PH20PH20PH20
7TX20TX21TX21
Holes Worksheet
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Select the cells of interest.

Activate Format|Cells >General >Custom

Enter for Type:

[=0]"";General
 

calgaryguy

Board Regular
Joined
Jul 10, 2006
Messages
116
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

Board Regular
Joined
Jul 10, 2006
Messages
116
Just thought I would see if anyone has any ideas on how to do this. See my example at the top. Thanks.

Ken
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886

ADVERTISEMENT

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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

Well-known Member
Joined
Sep 23, 2005
Messages
886

ADVERTISEMENT

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

calgaryguy

Board Regular
Joined
Jul 10, 2006
Messages
116
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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,038
Messages
5,545,672
Members
410,697
Latest member
srishtijain0708
Top