![](/i/fill.gif) |
![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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] none none> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
And lo On Sat, 12 Feb 2011 21:19:18 -0000, bart <bar### [at] home org> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
And lo On Sat, 12 Feb 2011 21:19:18 -0000, bart <bar### [at] home org> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
And lo On Mon, 14 Feb 2011 17:34:55 -0000, Darren New <dne### [at] san rr com>
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |