Replies

Clever, Steve, thanks.

Yippee!
Thanks, Hans, that was well worth a five-minute wait!
Didn’t figure the 2nd trip would leave the results of the first one still in place.

Macropod wrote:
If the data to be displayed are variable, you can even attach a formula to the text box via the formula bar, and the results will show in the text box.

How, please? See mind-bending example, attached!

=round(a1+0.5,0) where a1 contains the target number also works:
1.21 2
3.89 4
568.3 569
457825.7 457826
but this one may not be what you had in mind!
4 5

Yes, Hans is right.
My 2 penn’orth for what it’s worth is to save the source data list as .csv – file size will be much smaller, any possible complications from formulas in any fields will be overcome, as the .csv will freeze the result as a value.
Word will read the csv ok, so anyone else using the data source doesn’t even need excel.

Thanks very much, Steve.
The column concerned is in ascending order (they’re member numbers).
I’ve added the ‘false’ parameter (by zero, not false) to the formulas concerned, and hey, presto, they now work (on xl 2000) for all values of member number.
Can’t understand this, but some things don’t need to be understood if they work.
Will do same trick on xl 97 box at home and have every confidence…

Glad this thread has appeared, as I’ve recently come across a problem with this function (or me, or excel, both 97 and 2000)
I have a biggish (~3MB) file containing formulae like this
=IF($B$6=””,””,VLOOKUP($B$6,excumas!$E$25:$K$524,2)), where it looks up data from a range on a sheet called excumas.
The second column – the one I want – I want contains folks’ names.
It all works swimmingly for a value of B6 up to 333, but beyond that returns the value zero – that is, number zero.
Similarly other fields corresponding to values of B6 over 333 (which happen to be numbers, anyway.)
There’s nothing wrong or odd about the data in the looked-up list.
This is annoying and dangerous.
Is excel getting ‘tired’, are there limits to the size of a looked-up list, what’s the workaround?
Thanks!

Another option is to truncate the formula to this much
=”Availability by Brand Family -” & A8 & ” – ”
and put it right-aligned in cell J8, say. Then in K8 copy the word ‘Premium’ from wherever it is, left aligned, and distinctively coloured to taste.

Kislany,
While we’re waiting for the clever guys, a possible stopgap workaround might be to use conditional formatting to apply a colourful or distnctive formatting to the whole contents of the cells concerned, and/or the cells themselves? Or by hand, if necessary.

Thanks for these ideas, chaps.
The bit about font size is certainly a factor, it seems: 10pt takes it all, 11 point all but the last row, and from then on it gets worse.
The bit about an imagined paper size may not be true: pasting the copied object on a custom page pre-set at 299 x 558 doesn’t help.
Furthermore, if the object is copied into Page Plus direct or via Word, the irritating (and fatal) loss of the reversed-out formatting still stands.
Would it be useful to transfer this thread onto ‘General Office Solutions’?
I’m hugely surprised that what seems to be very straightforward is so hard!
May have to use XL as the page layout application after all….
There’s a first for everything….
(Wasn’t the ‘1-2-3’ of the Lotus product an indication of its alleged versatility – wordprocessor, drawing package and spreadsheet all bundled up into one?)

Thanks very much, Steve!
Said my brain was out of gear, and trying to make it too hard!
Happy Christmas!

Thank you all very much indeed. This thread has been
a) useful, and interesting, especially because of the hare which I hadn’t intended to start running!
As I’ve got to do a rejig of my application to handle data from a different source shortly, I think I’ll try it both ways, and see which my users prefer.
Strikes me that if MS Query is not installed by default, that’s an issue (but scarcely hard to rectify.)

Looks like vlookup might be the function you need to do the work.
The Help thing is acually quite helpful (on this occasion).

Thanks v much, Wassim.
It might help if I were clearer about the Bigger Picture:
The source csv file is the output from a DOS accounts package, and contains 115 fields and at present about 300 records (this will grow as membership grows, but is unlikely to breach the 65,000-record for ‘some time’.) Total size ~250kB, or 25kB zipped.
The ‘target’ application is an xl workbook which looks at the data in about 20 of these fields (non-contiguous), which are about people, where they live and how old they are, when they joined and aspects of their finances like their savings, their loan and the repayment history of their loan.
From this, do_it_all.xls (modest filename, eh?) does a number of useful things, like adding up total savings and loans, producing an insurance report in which members’ ages are an element, looking for dodgy loan repayment history and generating suitable letters, alerting to rising – x’s where the x’s are watershed ages, and there are more planned, like creating charts. Another useful thing it does is work round the insistence of Bill Gates that anyone with a date of birth like 23-12-18 must have a negative age!
So far it’s about 2 MB so a bit big to transmit by email to other users, even if zipped (c. 0.5MB) And this will get worse.
It’s designed for people like cashiers who aren’t particulalrly computerate.
Therefore there are a number of ‘point and shoot’ macros triggered by big buttons, and hyperlinks.
At present the most ambitious macro is the one which fetches the fields it wants from the source data, reformats that data so that dates look like dates and money looks like money, pastes it into my toy (replacing the existing data), and closes the csv file.
My question is, is this the most sensible way of doing it, or would the ‘get external data’ trick in the Tools menu be more efficient?

If you don’t want the ‘copy’ to be editable (sometimes this is dead handy!), an easy way is to select the range you want, copy, open a new workbook, and copy it as a picture.
This is a quite well-kept secret.
Do it by mousing to edit while holding down shift key, then choose paste as picture or I think there’s a link option (which you prob don’t want.)