Manipulating cell data when entered

[Timulo]

Hope someone is going to be able to assist - and hope I am describing this correctly. It sounds like a simple problem but it's proving to be hugely frustrating.

I'm putting together a workbook to keep track of income generated from Contractors (I work in recruitment). I have an existing system that displays margin, hours worked and income (margin * hours) but what I am looking for is a neater solution that would still have the margin column, but when hours are entered into a cell it is factored by the margin and displays income in the same cell.

So to be sure I am describing correctly, I am after a formula that will enable me to

1. Enter a value into a cell (for the hours worked - lets call this B1)
2. Multiplies this value by another cell (a fixed amount, the margin - lets call this A1)
3. Displays this result (B1 * A1) in cell B1, the same cell as the hours were entered in.

thanks

[bodnarj]

Hi Timulo,

I don't see anyway to do that. When you attempt to mupliply the input cell by a factor and then put the result in the same cell, you are creating a circular reference, and it will not work.

I think you are best to load the data into one sheet, and then set up a report on the second sheet. There, you can do the calcs, or present it the way you need.

[Kaper]

Well, Basically the idea is against standards, and against normal excel usage.

My honest proposition is:
try to input your hours and margin in respectively columns A and B (say A2:A10 and B2:B10)
Then, the sum of A2*B2+A3*B3+...+A10*B10 can be calculated with excel function =SUMPRODUCT(A2:A10,B2:B10)

But - if there is some rationale behind - OK.
It is possible with some programming
See attachment
In the worksheet Arkusz1 code is procedure (event handler for Worksheet event Change):

Please Login or Register  to view this content.