![](/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 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) |