I'm comfortable with formulas, but don't know how to make something 
formula-like which works on data entered into the effected cell.

    A   B   C   D
1  5   *

For instance, I want to enter a value into B1 and have the value from A1 
added to it. Any help?

  -Shay

Post a reply to this message

On 12/02/2011 6:18 PM, Shay wrote:
> I'm comfortable with formulas, but don't know how to make something
> formula-like which works on data entered into the effected cell.
>
> A B C D
> 1 5 *
>
> For instance, I want to enter a value into B1 and have the value from A1
> added to it. Any help?
>

I think that you will need to write some code in VB like a Macro, that 
does that. Sorry I can't help more but I couldn't code my way out of a 
wet paper bag.

-- 
Regards
     Stephen

Post a reply to this message

On 02/12/2011 06:18 PM, Shay wrote:
> For instance, I want to enter a value into B1 and have the value from A1
> added to it. Any help?
>
You could try to start with something like this VB macro code for 
ThisWorkbook object:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
         ByVal Source As Range)
    If (Source.Address = "$B$1" _
    And Sh.Name = "Sheet1") Then
      Application.EnableEvents = False
      Source.Value = Source.Value + Cells(1, 1).Value
      Application.EnableEvents = True
    End If
End Sub

Is there a real need for this, why not to use for example
simple formula in extra cell "=B1+A1"?

Post a reply to this message

http://www.homeandlearn.co.uk/excel2007/excel2007s4p1.html

I think you want the SUM function, if I understand your requirements.


On Sat, 12 Feb 2011 13:18:18 -0500, Shay <sha### [at] nonenone> wrote:

> I'm comfortable with formulas, but don't know how to make something  
> formula-like which works on data entered into the effected cell.
>
>     A   B   C   D
> 1  5   *
>
> For instance, I want to enter a value into B1 and have the value from A1  
> added to it. Any help?
>
>   -Shay


-- 
Ian McDonald
Lean Agile .NET 4.0/MVC
Senior Application Architect,
Developer and Security Analyst

Post a reply to this message

Shay wrote:
> I'm comfortable with formulas, but don't know how to make something 
> formula-like which works on data entered into the effected cell.
> 
>    A   B   C   D
> 1  5   *
> 
> For instance, I want to enter a value into B1 and have the value from A1 
> added to it. Any help?

You can't easily put a value into B and add A to it.

Better would be to have C1 have the formula =A1+B1. That's kind of how 
spreadsheets work.


-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

And lo On Sat, 12 Feb 2011 21:19:18 -0000, bart <bar### [at] homeorg> did spake  
thusly:

> On 02/12/2011 06:18 PM, Shay wrote:
>> For instance, I want to enter a value into B1 and have the value from A1
>> added to it. Any help?
>>
> You could try to start with something like this VB macro code for  
> ThisWorkbook object:
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, _
>          ByVal Source As Range)
>     If (Source.Address = "$B$1" _
>     And Sh.Name = "Sheet1") Then
>       Application.EnableEvents = False
>       Source.Value = Source.Value + Cells(1, 1).Value
>       Application.EnableEvents = True
>     End If
> End Sub
>
> Is there a real need for this, why not to use for example
> simple formula in extra cell "=B1+A1"?

or if you need to keep the layout add the data directly and use it as a  
basis for a Pivot Table and create a calculated field of =A+B then  
re-format it how you like.

-- 
Phil Cook

--
I once tried to be apathetic, but I just couldn't be bothered
http://flipc.blogspot.com

Post a reply to this message

And lo On Sat, 12 Feb 2011 21:19:18 -0000, bart <bar### [at] homeorg> did spake  
thusly:

> On 02/12/2011 06:18 PM, Shay wrote:
>> For instance, I want to enter a value into B1 and have the value from A1
>> added to it. Any help?
>>
> You could try to start with something like this VB macro code for  
> ThisWorkbook object:
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, _
>          ByVal Source As Range)
>     If (Source.Address = "$B$1" _
>     And Sh.Name = "Sheet1") Then
>       Application.EnableEvents = False
>       Source.Value = Source.Value + Cells(1, 1).Value
>       Application.EnableEvents = True
>     End If
> End Sub
>
> Is there a real need for this, why not to use for example
> simple formula in extra cell "=B1+A1"?

As an aside I'd use a "Worksheet_Change" on the sheet itself and "If Not  
Intersect(Target, Range("B:B")) Is Nothing Then" and "Source.Formula" to  
maintain the added value. Good that you remembered  
"Application.EnableEvents = False" otherwise the scripted change will  
trigger the script again and it's infinite loop time; so easy to forget :-)

-- 
Phil Cook

--
I once tried to be apathetic, but I just couldn't be bothered
http://flipc.blogspot.com

Post a reply to this message

On 02/14/2011 11:08 AM, Phil Cook v2 wrote:
 >As an aside I'd use a "Worksheet_Change" on the sheet itself
Yes, it seems more practical, especially in case of many worksheets.

 >and "If Not Intersect(Target, Range("B:B")) Is Nothing Then" and 
"Source.Formula"
 >to maintain the added value.
Yes, as I said, it's a start that could be extended to fit one's needs.

 >Good that you remembered "Application.EnableEvents = False" otherwise
 >the scripted change will trigger the script again
 >and it's infinite loop time; so easy to forget
That's exactly what happened with the first try ;)

There are many ways of doing things in Excel, I wander
what is the real reason behind the original question,
since it looks like a minimal example.
As possible application I could think of, say
- some kind of non-trivial custom auto-correction;
- instant conversion of typed formula to value;
- some tricky content-based auto-format...

Post a reply to this message

Phil Cook v2 wrote:
> or if you need to keep the layout add the data directly and use it as a 
> basis for a Pivot Table and create a calculated field of =A+B then 
> re-format it how you like.

Or enter the actual data in column ZZ and use B1 =A1+ZZ1. Or stick the 
actual data you typed on  separate worksheet. (Does that even work? Never 
tried it.)

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

And lo On Mon, 14 Feb 2011 17:34:55 -0000, Darren New <dne### [at] sanrrcom>  
did spake thusly:

> Phil Cook v2 wrote:
>> or if you need to keep the layout add the data directly and use it as a  
>> basis for a Pivot Table and create a calculated field of =A+B then  
>> re-format it how you like.
>
> Or enter the actual data in column ZZ and use B1 =A1+ZZ1.

But the advantage of the pivot table is that you can create whatever  
calculated fields you like and then just drag them on or off to show  
whatever data you want. You also don't have to worry about the size of the  
data field if you just pick an (A:D) range the table will not show the  
blank spaces. With a replicated data sheet the formulas need to be added  
for each row or show a load of blanks. Set it to AutoRefresh on Load or  
Print and you don't even have to worry about that.

> Or stick the actual data you typed on  separate worksheet. (Does that  
> even work? Never tried it.)

Yes it's a common technique to put 'workings out' on another sheet and  
then hide it; the formula is "=SheetName!Cell"

-- 
Phil Cook

--
I once tried to be apathetic, but I just couldn't be bothered
http://flipc.blogspot.com

Post a reply to this message