![](/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) |
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) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
And lo On Mon, 14 Feb 2011 16:13:24 -0000, bart <bar### [at] home org> did spake
thusly:
> 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,
Oh hell yeah; I wasn't offering negative criticism. The problem is that
when anyone offers a solution it can be slavish followed without an
understanding of how it works; then it mutates to fit a situation rather
than reworked. Offer a different approach and hopefully people might take
a closer look and ask "What's the difference?" :-)
> 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...
As mentioned the normal case would be putting A+B into the next cell. It
would be possible to hide the entire B column when printing if that was
the sole purpose; but I think a PivotTable provides the most customisation
without getting too deep into VBA.
--
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) |
Phil Cook v2 wrote:
> But the advantage of the pivot table
OK. I'm not familiar with the details of how pivot tables work. Thanks!
> Yes it's a common technique to put 'workings out' on another sheet and
> then hide it; the formula is "=SheetName!Cell"
Cool. Thanks!
--
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 Wed, 16 Feb 2011 17:38:47 -0000, Darren New <dne### [at] san rr com>
did spake thusly:
> Phil Cook v2 wrote:
>> But the advantage of the pivot table
>
> OK. I'm not familiar with the details of how pivot tables work. Thanks!
I don't think many do, which is a shame. I'd bet a lot of duplication of
data occurs because of this. Well worth playing with them
>> Yes it's a common technique to put 'workings out' on another sheet and
>> then hide it; the formula is "=SheetName!Cell"
>
> Cool. Thanks!
Nada. If you really want the sheet to be hidden rather than use the
toolbar a quick bit of VBA will stop it showing to causal users who
understand the Hide|Unhide command - Sheets("SheetName").Visible =
xlVeryHidden attached to the Workbook then password protect the module.
--
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) |
>> Yes it's a common technique to put 'workings out' on another sheet and
>> then hide it; the formula is "=SheetName!Cell"
>
It also could be placed in another file, like
"='C:\temp\[Book1.xls]Sheet1'!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) |
And lo On Thu, 17 Feb 2011 14:53:02 -0000, bart <bar### [at] home org> did spake
thusly:
>>> Yes it's a common technique to put 'workings out' on another sheet and
>>> then hide it; the formula is "=SheetName!Cell"
>>
> It also could be placed in another file, like
> "='C:\temp\[Book1.xls]Sheet1'!A1"
or =[FileName]SheetName!Cell if it's in the same folder; just makes folder
integrity a little easier if you're having to pass it on to someone else.
--
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) |