Replies

The cell formula is =left(string,num_of_characters) to return a string starting from the left.
e.g. LEFT(“Hello world”,1) returns “H”
LEFT(“Hello world”,3) returns “Hel”
If num_of_characters is omitted it is assumed to be 1.

The VBA formula is the same but if the number is omitted you get a zero length string, “”

Ewan.

Why would turning on worksheet protection have have no effect? (true if people have to work during w/ends and hols)
Ewan.

Plan B would be to swap the drives and cables to another known working PC just to rule them out of the equation

Thanks for the response,
I’ll need to dig out my Access For Dummies book and give it a go . Incidently i believe you put “[hh]:mm” to prevent the 24 hour limit.
Ewan

Assuming dates are along the top, you could set up the basic formatting for the normal days then use conditional formatting to shade weekends and holidays. The only problem with conditional formatting is you can’t specify heavy weight lines.
In the attatched workbook i have a named range of public holidays.
The first condition for each cell in the rota is set to shade itself if the date at the top of the column is a saturday or sunday.
The second tries to match the date at the top with a list of holidays and shades a different colour.

This way users can drag and fill to their hearts content.

Don’t even have a scanner but i’m guessing the accessory provides a backlight for transparencies, this means the colours will be correctly scanned.
Ewan.

I take it you re-opened the case and checked for obvious stuff like dislodged connectors to drives etc?
Ewan

Apart from the right-button drag technique there are a couple of other options that spring to mind:
1) Define a style for the formatting of the weekend and public holidays, then use the worksheet or workbook events (e.g. workbook_close) to trigger a short VBA routine. This routine re-applies your defined styles to the appropriate areas.
2) Assuming no-one should enter data for w/ends and holidays: ensure all cells that can be changed by a user are UNLOCKED. Then turn on worksheet protection.
Ewan.

A complete shot in the dark (as i have never built a PC from scratch or played about with the BIOS that much) what about the BIOS settings for cylinders/sectors etc for each drive? Are the manually set, auto, disabled etc ?
Ewan

Excel workbooks have event triggered routines, including “Open” (some call them Auto_Open etc), use these automatically display forms.
Worksheets can be individually hidden.

Ewan

10 factorial is (according to windows calculator) 3,628,800 but the INTEGER data type is for values -32,768 to 32,767. Hence runtime error 6, which is overflow. See help on data types and trappable errors.
Ewan

If you search through the WOW archives i’m sure there was a series of articles about styles and numbering, especially for law firms.
Ewan

Bit of lateral thinking…
how about half past 12!!! That’s greater than 12 AND less than 1(pm).

Are you refering to the way Excel will not appear to display more than 256 characters in cell if it is formatted as TEXT?
The contents appear in the formula bar ok, but you get a single row of # characters displayed in the cell, regardless of column/ width and wrapping settings. The only way around it is to set the formatting to GENERAL.
Ewan

John
I just realised in one workbook i use, the database, criteria and extract ranges are all on there own sheets. In this workbook the button is located on the criteria sheet and it still works. So it seems as long the advanced filter is run from code you can be anywhere in the workbook. Strange how the menu method is crippled in this way. Must be MS plot to make us all VB wannabes!!!
Ewan