2

I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across the same product lots of times and I don't want to have to keep adding it up in my head, then adding each one to the old number or having a column for each day.

For example, day one in the ullage diary, one vodka is spilled, so I input that. Then, I get a few days in and another two vodkas are spilled. Is there a way of inputting the second number in the same cell without having to do mental arithmetic?

This is really a very simplified example and obviously the numbers involved are super easy to add together, it's more about getting the most out of Excel and not about me not having the mental capacity to add two very small numbers together, because I do. Honest.

In my head I thought it would be good if once you select the cell if you hold alt and whatever you type would be added to that number.

7 Answers 7

7

There IS a simple way of doing this.

Say you have 50 in the cell in question, and want to add 30 to it.

Type 30 in a random cell somewhere else. Copy (Ctrl-C) that cell

Then the clever part: Right-click on the cell with 50 in it, and select Paste Special. In the box that comes up, select 'Add'.

This will add the value directly, without you having to do any mental arithmatic

Hope that helps.

1
  • Unfortunately this doesn't work if you've selected multiple cells.
    – ChrisF
    Aug 30, 2018 at 11:51
5

If you select the cell and edit in the function window you can do math, easly.

=1+4-2 

enter image description here

I am not aware of anyway short of some more complex reporting approaches to meet your goal.

There are a number of other ways like creating forms and such, but I get the impression you are looking for a simple solution.

1

Not easily. This would require VBA or a macro as cells within Excel don't have a "memory" to retain what was already in that cell.

Excel really isn't the correct method for tracking inventory this way. An Access database may serve a better purpose.

1

You cannot do exactly what you wish, but something very easy with a macro would be to set-up a column for input, then copy-paste with the add option on and clear the content. Put a button to run the macro. The advantage is that if you input several numbers you can check them before adding. For example if your data start in A1, put a button in b1 with the appended following code.

Range("a1:a4").Select
Selection.Copy
Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
    False, Transpose:=False
Range("a1:a4").Select
Selection.ClearContents

Let say you want to add 23 & 3 to the ale and stout inventory, respectively. Once you've finished you just press the button.

enter image description here

And you'll get this result:

enter image description here

0

There are many methods to do that, but I'll go with the simple and quick methods to do it.

Method 1:

You can use Excel formula to do to calculate inside the cells, you just need to click on a cell and type = then do the calculation and press Enter.

Example : =1+1

(This will show the sum of 1+1)

The problem with this method, is that you'll have to edit the cell each time you want to decrease or increase the numbers. For instance, let's say we have 25 beers in stock, and we wasted 5 of them, then the formal will be written like this =25-5 to get the result of 20, and another day we wasted 2 beers, so we'll go back to the cell and edit the formula to be like this =25-5-2 OR =25-7 and when we press Enter, it'll be shown 18.. you do this process all over again each time you want to edit the results, and this method is the simplest one.

Method 2:

Excel uses cell references, and cell references is an indicator of which cell is selected. For example, if we say that we need to select the second cell of the second row, then that means and indicator to cell B2. Because the columns were sorted by alphabetical letters, and the rows sorted by numbers, so B2 means column #2 at row #2. That how cell references works. In fact, we can use cell references with variables numbers better than Method 1.

To use this method, you need to combine Method 1 with this method and only replace the numbers with the cells references.

Example : =A1+A2

In the previous example, it'll show the sum of the cell#1 and cell#2 that are under column A, so whatever number in those two cells, will be added together and show the final result into the cell that you wrote the formula in. This method it'll give you what you need without any extra work.

I've prepared a sample excel file that will be a good demonstration using Method 2 to start with, so you'll know how it works.

Download

-1

OK, so I have been toying with this for the past couple days. I think I finally have a fun work around.

Use circular references, but limit the iterations to 1. This way the cell references its own value and adds what you put in the addition cell, but it will only do it one time.

The only scary thing about this is if you make an error, it will calculate that instantly and you lose your previous value. To fix this, just hit undo and correct the error.

I may be late to the party, but hey, somebody may want to know.

1
  • Can you give an example of how to accomplish this?
    – fixer1234
    Jul 10, 2015 at 17:02
-1

You might find the Spin Button works well for this. You can click it and increase or decrease amounts. It's like the rating button next to these answers. You can get to this via the developers tab. Google spin button and see if its what you're after :)

It looks like this

1
  • 1
    Can you expand on how the OP would introduce these buttons? Currently your answer is very low quality.
    – Burgi
    Jun 8, 2017 at 8:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .