On Sunday, December 03, 2006 5:25 PM [GMT+1=CET], Nino Pereira
<[EMAIL PROTECTED]> wrote:
Harold,
thank you very much for looking at this problem in such a detailed
manner, and in the process teaching me various things I had long
wondered about, viz., the 'absolute' referencing. That will be
helpful for years to come. Of course I should have read the docs,
but you know how it is: if you need something right now, you don't
spend a lot
of time reading about all kinds of things that seem irrelevant for
the problem at hand.
So, apparently, the problem is in the 'array formulas' that don't
do exactly what you might think they should. That looks indeed
like a bug (and not a feature: is this something the developers
should be made aware of? or, do they know because they may follow
our conversation?).
One problem remains: I can't use your work-around they way you
describe it. When I click on a cell I don't get the little + sign,
and dragging doesn't do anything. What I do instead is copy the
cell contents to some kind of internal storage, a clipboard
(with ctrl c), move over to the next cell, and then copy
it there (with ctrl v). I have become very adept at this, alternating
(ctrl V) with the side or down arrow. Copying this way works also
for a whole array, so once you have done (in this example) the days,
you can repeat it for the various contracts. And, once you have
this, you can do it for the entire two-dimensional field. FYI, I have
attached the final result.
From my parochial perspective this solves my immediate problem.
Still, doing it this way is more time consuming than with the
array feature, which is exactly why I loved it when I found out
that it existed. Too bad it doesn't work as I thought it should.
Thank you again. Please let me know if I should follow up on
this in any way.
Nino
<snip>
Nino,
1. Someone else mentioned using the ISNUMBER function. I have confirmed
on my own system that this makes your problem go away even with array
formulae. So "=10*ISNUMBER(A1:A10)" works with nuls/text in A1:A10.
2. Perhaps I wasn't specific enough about how to do the copy/drag thing.
Enter a set of numbers into say A1:A10. In B1 put "=10*A1" (without the
quotes) and hit the Enter key. Now click in cell B1. You should get a
thickish black border round the cell and a tiny black square in the
bottom right corner of the cell. Put the cursor, carefully, over this
black square; the cursor should change to a plus sign. When it's a plus
sign, press and hold down the left mouse button and drag downwards to
B10. As you drag down, the cells you pass over will acquire red borders.
When you get to B10 release the mouse button. The cells B1:B10 will turn
black. Click the left button and the cells will acquire correct values
i.e 10 times the corresponding A cell with zeros for places where the A
cell is null/text. You do need a steady hand for this ;-)
Regards, Harold
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
