random cell recognition for formula

[BlueDingo]

I have a spreadsheet where I need the end result to be auto filled after recognition of an adhoc "top up" amount.
How do I get E7 (3170) to automatically recognise the insertion of C7 (3000) to give this result without having to manually update the formula in E7?

Thank you,
Regards, Pete

DATE	PURCHASE ORDER	PRODUCTIVE HOURS against PO	HOURS USED	HOURS REMAINING
30-Aug-12	abc	2000		2000
01-Sep-12			490	1510
15-Sep-12			190	1320
22-Sep-12			600	720
02-Oct-12			550	170
11-Oct-12	xyz	3000		3170
15-Oct-12			200	2970
26-Oct-12			270	2700
01-Nov-12			500	2200

<COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2496" width=78><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2432" width=76><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4128" width=129><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 2944" width=92><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><TBODY>
</TBODY>

 

[Aladin Akyurek]

I have a spreadsheet where I need the end result to be auto filled after recognition of an adhoc "top up" amount.
How do I get E7 (3170) to automatically recognise the insertion of C7 (3000) to give this result without having to manually update the formula in E7?

Thank you,
Regards, Pete

DATE	PURCHASE ORDER	PRODUCTIVE HOURS against PO	HOURS USED	HOURS REMAINING
30-Aug-12	abc	2000		2000
01-Sep-12			490	1510
15-Sep-12			190	1320
22-Sep-12			600	720
02-Oct-12			550	170
11-Oct-12	xyz	3000		3170
15-Oct-12			200	2970
26-Oct-12			270	2700
01-Nov-12			500	2200

<TBODY>
</TBODY>

Looks like...

Control+shift+enter, not just enter:

=INDEX(E2:E100,MATCH(MAX(C2:100),C2:C100,0))

Note that this will return the E-value corresponding to the first instance max C-value.

 

[Michael M]

Try

Excel 2003
	A	B	C	D	E
1	DATE	PURCHASE	PRODUCTIVE	HOURS USED	HOURS
2		ORDER	HOURS against PO		REMAINING
3	30-Aug-12	abc	2000		2000
4	1-Sep-12			490	1510
5	15-Sep-12			190	1320
6	22-Sep-12			600	720
7	2-Oct-12			550	170
8	11-Oct-12	xyz	3000		3170
9	15-Oct-12			200	2970
10	26-Oct-12			270	2700
11	1-Nov-12			500	2200
12
Sheet3

Cell Formulas
Range	Formula
E4	=IF(C4>0,E3+C4,E3-D4)
E5	=IF(C5>0,E4+C5,E4-D5)
E6	=IF(C6>0,E5+C6,E5-D6)
E7	=IF(C7>0,E6+C7,E6-D7)
E8	=IF(C8>0,E7+C8,E7-D8)
E9	=IF(C9>0,E8+C9,E8-D9)
E10	=IF(C10>0,E9+C10,E9-D10)
E11	=IF(C11>0,E10+C11,E10-D11)

 

[BlueDingo]

Looks like...

Control+shift+enter, not just enter:

=INDEX(E2:E100,MATCH(MAX(C2:100),C2:C100,0))

Note that this will return the E-value corresponding to the first instance max C-value.

Thank you Aladin, but not sure what cell this suppose to be entered into.
Have tried a few options, but am now OK with a result.
Again, thnk you

 

[Aladin Akyurek]

Thank you Aladin, but not sure what cell this suppose to be entered into.
Have tried a few options, but am now OK with a result.
Again, thnk you

I apparently misread the question.

The following in E3 would work to:

E3, copied down:

=SUM(N(E2),-D3,C3)