Need some help with formulas please! I suspect what's needed is some sort of
IF statement.

I'm maintaining a list like this:
1 2 3 4 5 6
Item date POR PORValue amount PORremaining
Item 1 31-Aug 001 100 100 0
Item 2 31-Aug 002 500 200 300
Item 3 31-Aug 002 300 100 200
Item 4 18-Sep 003 1000 200 800
Item 5 30-Sep 003 800 300 500
Item 6 30-Sep 004 500 200 300
Item 7 16-Oct 004 300 100 200
Item 8 20-Oct 002 200 100 100
Item 9 25-Oct 003

I need a formula so that when I enter item 9 with the POR=003, it "knows"
that the PORvalue equals the PORremaining from the last entry where POR=003
and when I enter the amount (col 5), it calculates the next column
'PORremaining'.

I also want the values to work if I sort the data differently, e.g by POR
(col3).

I hope I've explained this clearly enough!

Much appreciate your help.

David

Hi,

I don't understand. We have entered 003 against item 9 in column 3 what
numbers do you expect in columns 4,5 & 6 and how are they derived?

Mike

"DavidF" wrote:

Need some help with formulas please! I suspect what's needed is some sort of
IF statement.

I'm maintaining a list like this:
1 2 3 4 5 6
Item date POR PORValue amount PORremaining
Item 1 31-Aug 001 100 100 0
Item 2 31-Aug 002 500 200 300
Item 3 31-Aug 002 300 100 200
Item 4 18-Sep 003 1000 200 800
Item 5 30-Sep 003 800 300 500
Item 6 30-Sep 004 500 200 300
Item 7 16-Oct 004 300 100 200
Item 8 20-Oct 002 200 100 100
Item 9 25-Oct 003

I need a formula so that when I enter item 9 with the POR=003, it "knows"
that the PORvalue equals the PORremaining from the last entry where POR=003
and when I enter the amount (col 5), it calculates the next column
'PORremaining'.

I also want the values to work if I sort the data differently, e.g by POR
(col3).

I hope I've explained this clearly enough!

Much appreciate your help.

David

Ok, I want column 4 to be 500 (column 6 - PORremaining - from the last entry
where POR=003). If I enter 200 in column 5, I want it to calcuate column 6 to
be 300 (500-200). And then if the next entry for POR=003 is item 12, say, I
want it to "know" column 4 for that entry is 300.

I hope that's a little clearer!

"Mike H" wrote:

Hi,

I don't understand. We have entered 003 against item 9 in column 3 what
numbers do you expect in columns 4,5 & 6 and how are they derived?

Mike

"DavidF" wrote:

Need some help with formulas please! I suspect what's needed is some sort of
IF statement.

I'm maintaining a list like this:
1 2 3 4 5 6
Item date POR PORValue amount PORremaining
Item 1 31-Aug 001 100 100 0
Item 2 31-Aug 002 500 200 300
Item 3 31-Aug 002 300 100 200
Item 4 18-Sep 003 1000 200 800
Item 5 30-Sep 003 800 300 500
Item 6 30-Sep 004 500 200 300
Item 7 16-Oct 004 300 100 200
Item 8 20-Oct 002 200 100 100
Item 9 25-Oct 003

I need a formula so that when I enter item 9 with the POR=003, it "knows"
that the PORvalue equals the PORremaining from the last entry where POR=003
and when I enter the amount (col 5), it calculates the next column
'PORremaining'.

I also want the values to work if I sort the data differently, e.g by POR
(col3).

I hope I've explained this clearly enough!

Much appreciate your help.

David