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

And lo On Mon, 14 Feb 2011 16:13:24 -0000, bart <bar### [at] homeorg> 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

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

And lo On Wed, 16 Feb 2011 17:38:47 -0000, Darren New <dne### [at] sanrrcom>  
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

>> 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

And lo On Thu, 17 Feb 2011 14:53:02 -0000, bart <bar### [at] homeorg> 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