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