Can someone please help! Formula Question

[nokmond]

Hi, i'm new to excel and only know the basics and i'm stuck! It's probably very simple - example, A1 is STOCK LEVEL and B2 is STOCK ISSUED, in A1 there is 30 and in B2 there is 0. How can i make it so when i change B2 to 1, A1 goes down to 29? Please can someone help me?

[VBA Noob]

Hi,

Try =30-B1

However I would use 3 columns. One for Stock, Stock Issue and the final one for balance e.g =A1-B1


VBA Noob

[nokmond]

I don't really want to use 3 columns. Can anyone help?

[VBA Noob]

Then use this in A1

=30-B1

VBA Noob

[nokmond]

Thanks it works a treat. Problem now is when a1 gets to 0 the formula is reset, can i stop this?

[nokmond]

Anyone?

[VBA Noob]

What do you mean reset.

Can you tell us what your expected results are etc

VBA noob

[nokmond]

hi, when a1 gets down to 10 i need to restock the product back up to 30 but when i enter 30 the formula stops working. thanks

[nokmond]

Along these same lines - is there a formula where it will take 1 from a1 and add it to b2 regardless of what figure is in a1?

[Bryan Hessey]

Along these same lines - is there a formula where it will take 1 from a1 and add it to b2 regardless of what figure is in a1?

Perhaps to explain why not,

'Formula' exists in a Cell and the calculated outcome of the formula is reflected in the cell.

A formula cannot 'poke' information to another cell, but can only present an answer which other cells may use.

A formula in A1 that uses B2 to set it's value, coupled with a formula in cell B2 that uses A1 to set it's value is classified as 'circular' (or recursive)

However, there is VB code that can perform what you ask, and the code can be triggered by a variety of methods, including a menu bar option, a button, or a Macro (Run the Macro) etc.

Such code could set A1 down 1 and B2 up 1

Does this help?
---

[rkaiser]

IF(('RACK #1'!I35-'RACK #1'!H35)<=19,0,'RACK #1'!I35-'RACK #1'!H35)

The restock variable "19" can be changed to reflect any number

The rack is just my location, normally is would say cell or something close

[Bryan Hessey]

IF(('RACK #1'!I35-'RACK #1'!H35)<=19,0,'RACK #1'!I35-'RACK #1'!H35)

The restock variable "19" can be changed to reflect any number

The rack is just my location, normally is would say cell or something close

The OP wants to keep Stock, Stock Issued and Stock level, but without using 3 cells, so I doubt that a second sheet would be acceptable. I think a button-triggered version of VBA Noob's formula to -1 from A1 and +1 to B1 will probably eventuate.

Just waiting for the OP to reply.
---

[nokmond]

hi, yes i want to just use 2 columns. I would appreciate more advise on how to do it. Thanks jon

[Bryan Hessey]

hi, yes i want to just use 2 columns. I would appreciate more advise on how to do it. Thanks jon

To use only two cells you will be (seriously) asking for problems, along the lines that you have no audit trail to follow, and no check to ensure accuracy that the three cells would provide, ie Start Stock - Used = Current Stock, you can check back to see what the Start Stock should be, and verify that the Current Stock is that 'on the shelf'

However, to use just two cells and increase one whilst reducing the other, you will need a trigger to 'Do' the subtract, usually a Button.

To add a button
Select View, Toolbars, Control Toolbox
The first icon toggles into/out of 'Design Mode'
In Design mode, select the Button and draw a shape where you want the button.
Rightmouse that button and select View Code and amend the code to look like

Please Login or Register  to view this content.

where A1 and B1 represent your two cells.

Rightmouse and select Properties and enter a Caption and select a Backcolour from the Palette tab.
Position and size the button to suit.

Exit design mode and clear that menu window, and then a click of the button will reduce/increase as required.

Hope this helps
---

[nokmond]

Thanks, i'll give it a try, might be a bit advanced for me tho!