Situation:-
I have Data in column "G" and "H" (please see belwo)
G H
G68 3980.00
C47 -4000.00
ME4 -200.00
ME4 200.00
PG2 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".


Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. Thanks

If you replace the value in column B then the lookup formula will be
overwritten - is this what you require?

How about just hiding columns you do not require?

--

Regards,
Nigel




"K" wrote in message
...
Situation:-
I have Data in column "G" and "H" (please see belwo)
G H
G68 3980.00
C47 -4000.00
ME4 -200.00
ME4 200.00
PG2 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".


Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. Thanks

Hi,

I think I understand and don't believe you need a macro, try this instead

A B C D
G68 3980 1000 1980
G68 3980 1000 1980

Column A is a manually input value
Column B formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)
Column C is a manually input number
Column D formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10))

The formula takes the original value and subtracts any manually input
values. As you will note this only does the first 10 rows but yu can extend
tha ranges to what you want.

Mike



"K" wrote:

Situation:-
I have Data in column "G" and "H" (please see belwo)
G H
G68 3980.00
C47 -4000.00
ME4 -200.00
ME4 200.00
PG2 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".


Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. Thanks

On Dec 28, 8:55*am, Mike H wrote:
Hi,

I think I understand and don't believe you need a macro, try this instead

* A * * * * * * *B * * * * * * * *C * * * * * * D
G68 * * 3980 * *1000 * *1980
G68 * * 3980 * *1000 * *1980

Column A is a manually input value
Column B formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)
Column C is a manually input number
Column D formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10))

The formula takes the original value and subtracts any manually input
values. As you will note this only does the first 10 rows but yu can extend
tha ranges to what you want.

Mike



"K" wrote:
Situation:-
I have Data in column "G" and "H" (please see belwo)
G * * * H
G68 * * 3980.00
C47 * * -4000.00
ME4 * * -200.00
ME4 * * 200.00
PG2 * * 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".

Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". *Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. *Thanks- Hide quoted text -

- Show quoted text -

Thanks for replying Mike. I want 1980 to come in column B when i enter
G68 next time in column A. Actually what i am trying to do is that in
code "G68" i have 3980 budget which come in coloumn B when i enter
"G68" in coloumn A and when i put 1000 or any other figure in column C
then in coloumn D i get balance that i have taken 1000 from 3980
budget but i want a formula that when i enter G68 again in column A
then i want to have same Vlookup formula in column B but this time i
want the balance figure which will be 3980 - 1000 = 2980 to appear in
column B so i know that i got that much budget left to spend

That's what my formula does. Put the 2 formula given in columns B & D and
then drag them down as far as required. For (say) project G68 every time you
put G68 in column A and a value in column C you will get the remaining budget
in column D

Mike

"K" wrote:

On Dec 28, 8:55 am, Mike H wrote:
Hi,

I think I understand and don't believe you need a macro, try this instead

A B C D
G68 3980 1000 1980
G68 3980 1000 1980

Column A is a manually input value
Column B formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)
Column C is a manually input number
Column D formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10))

The formula takes the original value and subtracts any manually input
values. As you will note this only does the first 10 rows but yu can extend
tha ranges to what you want.

Mike



"K" wrote:
Situation:-
I have Data in column "G" and "H" (please see belwo)
G H
G68 3980.00
C47 -4000.00
ME4 -200.00
ME4 200.00
PG2 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".

Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. Thanks- Hide quoted text -

- Show quoted text -

Thanks for replying Mike. I want 1980 to come in column B when i enter
G68 next time in column A. Actually what i am trying to do is that in
code "G68" i have 3980 budget which come in coloumn B when i enter
"G68" in coloumn A and when i put 1000 or any other figure in column C
then in coloumn D i get balance that i have taken 1000 from 3980
budget but i want a formula that when i enter G68 again in column A
then i want to have same Vlookup formula in column B but this time i
want the balance figure which will be 3980 - 1000 = 2980 to appear in
column B so i know that i got that much budget left to spend

On Dec 28, 9:43*am, Mike H wrote:
That's what my formula does. Put the 2 formula given in columns B & D and
then drag them down as far as required. For (say) project G68 every time you
put G68 in column A and a value in column C you will get the remaining budget
in column D

Mike



"K" wrote:
On Dec 28, 8:55 am, Mike H wrote:
Hi,

I think I understand and don't believe you need a macro, try this instead

* A * * * * * * *B * * * * * * * *C * * * * * * D
G68 * * 3980 * *1000 * *1980
G68 * * 3980 * *1000 * *1980

Column A is a manually input value
Column B formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)
Column C is a manually input number
Column D formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10))

The formula takes the original value and subtracts any manually input
values. As you will note this only does the first 10 rows but yu can extend
tha ranges to what you want.

Mike

"K" wrote:
Situation:-
I have Data in column "G" and "H" (please see belwo)
G * * * H
G68 * * 3980.00
C47 * * -4000.00
ME4 * * -200.00
ME4 * * 200.00
PG2 * * 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".

Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". *Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. *Thanks- Hide quoted text -

- Show quoted text -

Thanks for replying Mike. I want 1980 to come in column B when i enter
G68 next time in column A. *Actually what i am trying to do is that in
code "G68" i have 3980 budget which come in coloumn B when i enter
"G68" in coloumn A and when i put 1000 or any other figure in column C
then in coloumn D i get balance that i have taken 1000 from 3980
budget but i want a formula that when i enter G68 again in column A
then i want to have same Vlookup formula in column B but this time i
want the balance figure which will be 3980 - 1000 = 2980 to appear in
column B so i know that i got that much budget left to spend- Hide quoted text -

- Show quoted text -

sorry for being pain Mike but i think may be i am still not be able to
explain to you what i need. is there any way i can send you my
spreadsheet and explain everything on that spreadsheet so you can
understant better what i am trying to say

Hi

Upload the file here and then post the link

http://savefile.com/


Mike

"K" wrote:

On Dec 28, 9:43 am, Mike H wrote:
That's what my formula does. Put the 2 formula given in columns B & D and
then drag them down as far as required. For (say) project G68 every time you
put G68 in column A and a value in column C you will get the remaining budget
in column D

Mike



"K" wrote:
On Dec 28, 8:55 am, Mike H wrote:
Hi,

I think I understand and don't believe you need a macro, try this instead

A B C D
G68 3980 1000 1980
G68 3980 1000 1980

Column A is a manually input value
Column B formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)
Column C is a manually input number
Column D formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10))

The formula takes the original value and subtracts any manually input
values. As you will note this only does the first 10 rows but yu can extend
tha ranges to what you want.

Mike

"K" wrote:
Situation:-
I have Data in column "G" and "H" (please see belwo)
G H
G68 3980.00
C47 -4000.00
ME4 -200.00
ME4 200.00
PG2 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".

Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. Thanks- Hide quoted text -

- Show quoted text -

Thanks for replying Mike. I want 1980 to come in column B when i enter
G68 next time in column A. Actually what i am trying to do is that in
code "G68" i have 3980 budget which come in coloumn B when i enter
"G68" in coloumn A and when i put 1000 or any other figure in column C
then in coloumn D i get balance that i have taken 1000 from 3980
budget but i want a formula that when i enter G68 again in column A
then i want to have same Vlookup formula in column B but this time i
want the balance figure which will be 3980 - 1000 = 2980 to appear in
column B so i know that i got that much budget left to spend- Hide quoted text -

- Show quoted text -

sorry for being pain Mike but i think may be i am still not be able to
explain to you what i need. is there any way i can send you my
spreadsheet and explain everything on that spreadsheet so you can
understant better what i am trying to say