Cell with "0" value(hidden) and "Name" displayed

[Adas]

I'm kinda stuck,

I'm trying to use a cell for one of my tables. I need it to display "name" in the table and also give a "0" value where another table calculates total value for the row. Right now i get an "error" message.

Any ideas???

 

[Ralajer]

Adas,

As I understand your requirements you want to Display "Name" but have the value in the cell be '0'. If the "Name" is a fixed value your can use a custom number format to have the cell Display "Name" when the value is zero. In the format dialog go to Custom under the Number tab and set that Type to:

#,##0_);(#,##0);"Name"​

Any reference to the cell will only see the zero.

Although, it would be better to adjust your formula that "calculates total value for the row". It's up to you if you prefer the custom format method but if you would like to change the formula please post a sample of the data and the formula you are using.

 

[Adas]

I tryed the code you gave me and I do get the value of "0" but the cell does not show "name" on the cell. It's blank.

"Name"="Vacation"

This is for a schedule table I'm trying to create and this is the last thing I need to get done. I'm trying to get this done for tomorrow

 

[Ralajer]

The format will only be applied if a '0' is entered in the cell. Also change the format to (0;0;"Name";@) not really different for your purposes than the last but simpler.

Let me know if this works.

 

[Adas]

I have tryed both of your advices and no luck.

Whenever I key in the formula I get "invalid" or "value" error.

Here is how I have it set up:

In one table I have namesof employees for a one week schedule. Next every name I have columns for 7 days. In each column I have 2 cells for start shift and end shift. Those cells use another table where I have a listing of times from 12:00am till midnight with 15 min incroments. At the top of that table I also have a blank for a day off and a vacation.

When an employee of mine is trying to make a schedule they select the start and end shift. There is a column at the end of the week where I'm trying to have all the hours added up for each employee.

This is where I get stuck. If let say the select an 6 hour shift, it shows total hours for the week correctly but if I select "Vacation", it displays ##### (=Value).

How do I get the vacation cell to display "Vacation" and also have a value of "0" hours for my total hours scheduled column? I have tryed to link the cell next to it with a value of 0 but in "IF" I don't think I put the formula properly.....

PS.
To be honest with everyone I don't know anything about Excel and this is the first time I'm working with the formulas. I wish I could attach my file for everyone to have a better understanding of what I'm trying to accomplish.....PLease help me out

 

[Ralajer]

You can post a sample of the sheet with the MrExcel HTML Maker add-in. The link has direction on use. That will probably help me get a better idea of whats going on.

EDIT:
You can also upload your file to a file sharing site such as Wikisend http://www.wikisend.com/ and provide the download link.

 

[Adas]

Here is a link to the table I'm trying to do. Use "Data1"
Maybe you guys have a better understanding of what I'm trying to do and have any guesses what I can do.

http://wikisend.com/download/309712/Grocery Scedule-UnProtected.xls

 

[Ralajer]

In the AF:AL Range change the formulas to the following, update refs of course.

=IF(ISERROR((F11-D11)*24),0,(F11-D11)*24)*(COUNT(F11,D11)-1)

The IF checks if an error has occurred which one will if you try to add text mathematically. Returns zero if an error has occurred and hours difference otherwise. The count will result in the value being zero unless both times are entered, Vacation doesn't effect it.

Let me know how it works.