Replies

If you select the whole range, either Format/Cells or the Text to Columns Wizard will format the lot as text, easier than entering with a leading ‘. The trim function removes leading (and trailing) blanks.

If X is text you should use “X”.

Try this answer This link

Thanks Rory, that was it.

Found them! Here are two links it has taken me a while to find again. The one on charting is especially good.

Dynamic Ranges
Charting Dynamic Data

http://www.elementkjournals.com/ime/9603/ime96031.htmThis Link may be of help.

OK I surrender how do you enter those neat This Link messages without the whole URL showing?

The Offset/CountA method, useful for Pivot Tables and Graphs, should work here. If you set the print area to:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$,8)

XL should print 8 columns wide and as many rows as there are entries in Column B. May need modifying if the last row with an entry is in a column other than Column B.

If all the full names have the same number of names (first, middle, last) in them you can use the Text to Columns Wizard with space as the delimiter.

Andrew I am confused. If I put Fred, Mary, and Joe into cells A1, B1, & C1, then select A1 to C1, then use Format/Alignment/Horizontal/Centre Across Selection all that happens is that all three words become centred in their cells. If I put Fred in A1 and leave B1 and C1 empty, then select A1 to C1 etc, Fred appears centred across the three columns. Isn

Not quite sure what you mean. However the following macro will merge selected cells, or unmerge them if they are already merged. It emulates what was the standard merge/unmerge button in XL95 which was far superior to the mess in XL97, in my opinion.

Sub MergeToggle()
‘
With Selection
If .MergeCells Then
.MergeCells = False
Else
.MergeCells = True
.HorizontalAlignment = xlCenter

End If
End With

If you select your merged A-E cells, then run the macro, then select A-D and run it again, you should achieve what you want.

Use the camera (Tools menu/Customise/Commands/Tools, and drag the camera onto a toolbar). Select your first sheet, click on the camera, go to a blank sheet and drop and size a copy onto this sheet. Repeat for the second sheet.

Phil. When I tried Smartboard I thought it was wonderful, but I soon ran into problems when I tried to copy some not very big worksheets in XL, Smartboard could not copy the whole selection. Have you had similar trouble?

Suzanna. Thanks for the tip about F3; F3 or F5 seem to work equally well in this case. If you set range_lookup to false (as I always do) the table_array does not have to be sorted. I have never had any problems caused by including the column headings in the table_array.

Wonderful! Thank you Andrew. That has frustrated me ever since I moved to XL97.

SubjectCodes.xls contains the subject names and codes, and the range with all the data is named ‘Codes’.

Trial.xls contains a few codes.

Open both files.

If in Trial.xls, cell A2, you type “=VLOOKUP(A1,” (without the “s), then go to the Window Menu and choose SubjectCodes.xls, then Insert/Names, you will find all the options grayed out. However on occasion I have found the Paste option available, and on choosing this I get a list of Range Names which I can choose from, in the present case I would choose ‘Codes’. This is very convenient (and in XL95 you could do this everytime because the Name Box was available), but I cannot work out why in XL97 sometimes it is available and sometimes not.