|
Home > Archive > MOS > January 2001 > enter a negative value
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
enter a negative value
|
|
|
| Paisleyskye I wonder if you have answer to this; at work they want to use the invoice template in Excel, but they need to use a negative dollar amount (like a credit or deposit). Every attempt to enter -11,255.00 comes back with a message saying that you must enter a number or a zero. Trying to customize the invoice only drove me crazy. In the end I got the user to use word and enter the data where needed, looked pretty but didn't solve my problem. This is not mission critical, just annoying. However. We need to create about 15 manual invoices a month and using the Excel template would be nice. help, my supply of tylenol is diminishing!
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works" | |
|
| Peterket:
They only need to format the cells that have the negative values to have the numbers show as negative values.
In order to do this:
1. Select the Format menu and choose cells.
2. Or right click on the selected cell and choose format cells from the shortcut menu.
3. Select the Number tab
4. From the list select negative
I hope this helps.
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| Thanks, I will try it again on Monday.
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works" | |
|
| Let me know if you have any problems.
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| Well I tried to use the invoice template again. I modified the number cell just like you said, now when I enter -11255.00 I get a box that pops up "Unit Price - You must enter a number into this cell". I must be missing something here, I can't see this being that difficult (or is it?)
-frustrated
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works" | |
|
| Peterkett:
Try entering the number without the negative sign and then formatting the cell as negative like I told you to before.
Once you format the cell as negative, you shouldn't need to enter the negative sign. After you put the number into the cell and press Enter, the number should automatically be formatted as you have formatted the cell.
Let me know if this helps. If it doesn't, I have a few other ideas that might work.
I really think this is the problem though.
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| I tried every possible combination that I can think of. All I need to do is enter a dollar figure that would reduce the invoice total, and nothing seems to work for me. Using the quick help just says that I can select how negative numbers are displayed. But that doesn't mean that is how you enter the data as a negative (credit to customer). I did exactly like you said and even with a few variations, never knew that there so many different error messages in Excel.
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works"
[This message has been edited by peterkett (edited 01-14-2001).]
[This message has been edited by peterkett (edited 01-14-2001).] | |
|
| Peterkett:
This one has me stumped, but we are going to figure it out. This is where I'm at right now:
1. Have you tried bracketed negatives - make sure you format the cells that way?
There is another thing that you can try. But only on invoices, because you would not want to try it on anything that you couldn't change back to the way it was quickly.
You can tell Excel to change the values to match the formatting.
1. Select the tools menu
2. Select options and select the calculations tab
3. Choose Precision as Displayed.
Again don't try this on a major spreadsheet because it will change numbers. It should only be used when you are dealing with impercise data.
If neither of these work we'll try something else. I can't find anything in any of my reference books or in the Microsoft Knowledge Database - but we'll come up with the solution anyway.
And when we do find the answer, I'm going to add it to the Excel study guide, just because it is such a stumper, and other people may have the same problem along the way.
In the meantime, I'll keep looking.
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| Could it have something to do with the fact that it is a template and ALL the cells have a specific function?
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works" | |
|
| I'll check it out - I don't think so, but at this point anything is possible.
You have tried just entering the number into the cells eh? Once it is formatted as negative, you should be able to just put the number in without the minus sign, and when you push enter it should automatically become a negative number.
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| Is there a way to send over the template so some of us can take a look at it? It sounds like someone put some restrictions on the cells that are conflicting with what you need. | |
|
| sure, just click new office document then select invoice from spreadsheet solutions. Standard invoice template within Office 2000. Can't believe this is so hard. 
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works" | |
|
| In my experience, negative numbers on an invoice would result from a calculation (such as when the client has overpaid) and not by direct entry.
There could be Data>Validation restriction on the cell entry, limited to positive numbers.
I am not sure what you can do if it is a data validation restriction.
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| Your last message got me thinking about something and I found it. The procedure is easy once you find it.
Before you enter the data, select the cell, then select Data > Validation
on the settings tab, change the Validation criteria data setting from between to not between then click ok.
Now it is possible to enter the data and have it function as a negative value and reduce the invoice total by said amount.
Only thing is, you have to do it cell by cell.
So there it is people, the answer finally! Thanks for all the input. Case solved 
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works"
[This message has been edited by peterkett (edited 01-09-2001).]
[This message has been edited by peterkett (edited 01-09-2001).] | |
|
| Congratulations Peterkett!!!
I wish I could have been of more help to you, but the problem is solved and that is all that really matters.
Again, I'm sorry, I feel pretty useless since you wound up solving it yourself.
Nevertheless, I did my best, and I hope I can be of help to you in the future.
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| Paisleyskye there is no need for this
quote:
Again, I'm sorry, I feel pretty useless since you wound up solving it yourself.
If you hadn't mentioned quote:
There could be Data>Validation restriction on the cell entry, limited to positive numbers
I'd most likely still be looking for the solution. Even though you did not come up with the solution you did point me in the right direction. You deserve as much credit as anyone else. I just needed a push in the right direction, MS software isn't really my thing, I'm more of a hardware/OS type tech. Once again, thanks for the help, perhaps some day I can return the favour. Feel free to send an email if you ever need to.
Thanks again for the support 
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works"
[This message has been edited by peterkett (edited 01-09-2001).] | |
|
| Peterkett,
Thank you. I'm glad I was able to steer you in the right direction. Things must be a lot more relaxed at work now, eh?
------------------
Sincerely,
Paisleyskye
Best wishes and peace to all!!!
(MOUS,A+,MCPx4) | |
|
| Yes things are back to normal (sorta). The user who has to make the invoices is very happy now. She passes on her thanks, I showed her all the posts and she understands what we went through. Now I can concentrate on my job 100% with no distracting thoughts,,, until the next problem pops up that is.
Oh well, time to prep for the
Network+ exam - 4 days away.
------------------
Peter Kett A+ i-Net+
"technology is wonderful when it works" |
|
|
|
|