Replies

Thanks, Steve, have just replied to this but got timed out on a flaky Limey connection.
A chemistry lesson and a maths lesson in the same day is going some! What’re you like on 8th-century Serbo-Croat grammar?
I **think** your formula is missing a -1 on the end. Using the Goal seek tool on the case i = 0.01, L = 1000, R = 38 gives the anser nearly 30.7 payments, one fewer than your formula.
Repeating with other start values indicates a consistent discrepancy of one payment too many. If one instance is a fluke, isn’t lots the equivalent of what you intellectuals call ‘induction’ and the rest of us call ‘experience’?
Work piece attachement in separate post (trying to beat the timeout.)
Thanks again!

Well done, Steve, on a clear and succinct refresher course on first order kinetics.
I was once a chemistry teacher!

Well done, Steve, on a clear and succinct refresher course on first order kinetics.
I was once a chemistry teacher!

Or, using the quick and dirty approach, why not conditional format the cells you want filled in bright red while they’re blank, but ‘normal’ when they’ve been completed.
Then your colleagues need only understand that you don’t want to see anything red in their completed handiwork.

Or, using the quick and dirty approach, why not conditional format the cells you want filled in bright red while they’re blank, but ‘normal’ when they’ve been completed.
Then your colleagues need only understand that you don’t want to see anything red in their completed handiwork.

Mega thanks, Steve, will work on this route at the weekend and come back if I need to.

Mega thanks, Steve, will work on this route at the weekend and come back if I need to.

Sorry late in thanking for these, chaps.
This job no longer top of the pile.
Will come back on this topic if need to.
Thanks again, very much.

Thanks, Steve.
So if the existing formula, which counts everything whether it’s filtered or not, is
=COUNTIF(D$18:D$2313,”1″)
(and subsequent cells look for the value “2” and so on), how do I combine this with the SUBTOTAL trick, preferably in a way which tolerates the filter option ‘show all’, ie no filter at all?
I figure I need a “SUBTOTAL IF” function, which only exists in my head!
It comes from a survey generating responses from 1 to 5, and I want to produce reports deriving from particular districts, age groups, and so on.

Whack it in about 8 ways, kevin, just to make sure!

I can’t replicate it either: XL 2000 sr1 (in a file generated in xl97 sr2).
Strikes me a good case if you’re doing something ‘ambitious’ to save as… [newname] and do it on the copied file first.
Once bitten, twice shy…
John

Yes, or even format a blank sheet with some empty cells done in a tasteful grey outline, and do a ‘copy as picture’ of that and paste on top of the image you’re using as the fake ‘watermark’.

This won’t do anything except alert, but is simple.
If the range you don’t want disturbed is, say, a1:j25, Name it something imaginative, like “Range”.
In K1, put a formula like
=IF(ROWS(range)=25,””,”Are you Sure”)
Copy this to K25 (for a more decorative effect.)
Apply a conditional format to k1: whatever hideous combination of bright red background, bright yellow text and thick blue border is most likely to wake your user up.
The conditional format to be triggered when
cell value is equal to =”Are you Sure”
A similar approach to alerting when columns were added could be used.

No, Hans – if I have only been messed up because the ‘record preview’ option does not show what will actually happen, I apologise for troubling you (but I hope you won’t feel badly of me.)
Will try again tonight, by by restoring your handiwork and physically printing the first 12 records, initially.
The ‘record preview’ option tells the truth with form letters – haven’t used labels much, before.
Used to use Works, which, er, works.
Thanks again.

Another advantage of using excel is that to some extent the form can be self-completing.
Try typing
15
into the attached – it will come up with my name. It gets this from a hidden sheet, from which I’ve removed all the other names and numbers for this purpose.
Type in some numbers, and it self-totals.
I’ve rubbed out the two macros – the ‘add another row’ does what it says, the ‘copy to new file’ pastes a picture of the form into a new spreadsheet, which has the merits that the file size gets a lot smaller, and it can’t be edited (not without text boxes and things, anyway!)
Just some thoughts.
I know that Word can do arithmetic in tables, but using a spreadsheet seems more intuitive?
Offering the form as a template (which the Lounge won’t let me do) makes it harder for users to rub out the formulas.