Can someone please help! Formula Question

**nokmond** · 09-30-2006, 08:40 AM

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** · 09-30-2006, 08:50 AM

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** · 10-01-2006, 04:47 AM

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

**VBA Noob** · 10-01-2006, 07:30 AM

Then use this in A1

=30-B1

VBA Noob

**nokmond** · 10-01-2006, 09:18 AM

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

**nokmond** · 10-02-2006, 06:43 AM

Anyone?

**nokmond** · 10-03-2006, 10:05 AM

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** · 10-03-2006, 07:59 PM

Originally Posted by 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?

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** · 10-04-2006, 07:59 AM

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** · 10-04-2006, 08:21 AM

Originally Posted by 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

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.
---