Formula using indirect

[sturnusek]

Hi all,

I need a little bit of a help to make a formula...

I might have two lines with the same component in a big list, and I want my stock figure to be updated in the second line based on previous calculation.

I tried to use indirect and ROW()-1, but had no luck.

So, I have a available stock figure made against a component in J2:J column, and the demand column in I2:I column.

There can be two lines using the same component, and I am in need of a formula that will minus the stock from the fist line I-J, and then the available stock figure to reflect the first transaction on the second line and so on, allowing it to run into minus values.

So I need the second line to have the first lines demand to be taken off the available stock - I can imagine this cannot be done without creating additional column.

Any ideas?

 

[James006]

Hi,

Your explanation is a bit confusing ...

May be you should try =Offset(yourcell,-1,0) ...

Hope this will help

 

[Tetra201]

Yes, this can be done with the INDIRECT function. For example,

=INDIRECT("R[-1]C",0)

will return the value of the cell immediately above the formula cell.