Replies

and the Format Painter tool doesn’t work, either, on inserted pictures.
JRR

No code at all, I fear.
JRR

You’re a star, Steve.
If you do a custom filter, the criteria handle wildcards: but not, it seems, the formula.
Not in the same way, anyway.
Thanks again
JRR

Steve,
>>>>What do you mean by datedif can not handle future birthdates?
Merely that it reports an error rather than returns a minus number.
“Age = -3 years” means, to me, “will be born in 3 years time from now”, rather more meaningful than the #NUM error returned by DATEDIF.
John

Thanks v much, Peter.
Concerning the ‘time component of TODAY(), it seems always to be zero (just like it sort of says it should be). It’s the NOW() function which includes ‘time elapsed so far’ within ‘TODAY’.
The TRUNC function or applying a suitable format works, of course, but don’t do what I want, which is to count down (or up) **grin** to say an impending birthday and it would be really good if the person became exactly 24, say, on the unique and exact date corresponding to his 24th birthday!
This is not a show-stopper but I’m annoyed with myself about having been unable, so far, to bottom it out.
Thanks again.
John

Try one of the following:

– Select Tools | Options…, and in the View tab tick the check box “White space between pages”, then click OK.

or

– Move the mouse pointer to the top of the document area, and click when the mouse pointer changes to an updown arrow .

Thanks v much, Hans, the second one worked fine.
JRR

cool, Hans.
Is there a way using an ‘event’ (eg any change to the results list) to provoke the pivot table to be refreshed?

Note: pivot tables aren’t updated automatically if the source data change. To update, click in the pivot table and click the Refresh Data button on the Pivot Table toolbar (the exclamation mark).
[/quote]

In W2003 & 2007, line number is a character style that you could modify to your specs.

In W2007 you’ll have to modify it in managed styles since it is hidden. Set the sort order to alphabetical to make the style easier to find. Select it and then modify it.

In the W2003 styles pane, click customs in the show drop down. Choose all styles for the category. find and put a check in the box before Line number. Click OK. Back in the styles pane, modify the style.

PamC

Thanks very much, both. Exactly what I wanted.
JRR

Then the answer is, as Hans said, no. Can you explain what you are trying to achieve – there might be a workaround using shapes for example.

I think this is what Rory meant, see attached.

I did it this way, in Excel 2003:
1. save your image somewhere.
2. create a rectangular autoshape ‘over’ the cell required.
3. in Format Autoshape | Colours and Lines choose Fill Effects.
4. Select Picture and navigate to your saved image and OK out.
5. back in Colours & Lines, choose No Line or whatever turns you on.
6. Choose Properties and select Move & size with cells and Print Object.
7. Choose the ‘cell’ (actually the autoshape).
8. In the formula bar, type the reference of the cell whose contents you wish displayed in the autoshape.
I’ve chosen F12, it could of course be H12 in my example.

H17 shows what’s in H17, but looks like it’s got your fancy background.
And H18 is just silly, but shows a variant.

Does this fake what you want?

John Rose

Thank you very much, Jan Karel.

Yes, this is a tedious chore.
If all you want is the person’s age, if the DoB is in B2, then this formula works:
=IF((TODAY()-B2)/365.25<0,(TODAY()-B2)/365.25+100,(TODAY()-B2)/365.25)
It detects the negative age quoted if the year of birth is returned as eg 2026, and adds 100 years to it.
JRR

It’s a structured list, Hans, 1st row = headers exactly as in the Foxpro table from which it came.
Plenty of them!
/memb_no surname forenam addres1 addres2 addres3 addres4 postcod tel_no occupat d_o_bir join_date feepaid nok_sur nok_fir nok_ad1 nok_ad2 nok_ad3 nok_ad4 nok_pcd emp_num sta_pay pay_frq post_to categories budget bfeeprev lintprev bintprev works_tel comment juvenile qualify nonmemb worksad1 worksad2 worksad3 worksad4 workspcd worksfax worksemail worksgroup worksdept worksrole worksni mobile title nok_tit nok_num home fax homeemail prevpc budgsusp delinquent dateleft alert multipay dwp1 dwpcat tofull sortcode account loanpcent absvalue uselpcent

I’ve edited the contents of the records with updated stuff looked up from a .csv file, copied and pasted as values, and want to save it back as a FoxPro .dbf with the same filename as the previous, thus replacing the old data with the new.
Not all records have non-null values in all fields – might it help if I populated such cells with a space character, for example?
Thanks for your help so far.
John

I fear that it doesn’t Hans, in any of the flavours of .dbf offered in the ‘save as’ box.
JRR

Thanks, Hans, am trying this now…

Thanks, Hans, solution 1 worked.
In my innocence, I’d looked in Tools | Options in Excel.