MS Excel advanced help please

[wh666-666]

Hi sharkies ..

Wondering if anyone can help out. At the moment im building a spreadsheet workbook up with a few features, intended to be passed to other people with limited knowledge, so therefor, I want it to do certain things automatically.

I already have the first worksheet handled, with macros implemented and the top few rows frozen as a pane.

However Ive got two niggling issues with the second worksheet:

- Ive got a horizontal few rows, with column title information in and then beside this running vertically down I have a legend explaining colours and technical terms. I would like the selection where the data is, for example c4:f299 to be scrollable, but the legend on the side and the header row at the top to stay where they are.

Ive tried freezing panes, even experimentally on one row and one column, which MS state can be done, but the column doesnt get frozen. Ive tried using splits but yet the column still scrolls and ive tried worksheet properties to set a scrollable area, yet this doesnt work either.

Im at a bit of a loss as to how to sort this as I need three scrollable columns of data but a legend beside it and a row as a header.


- Second problem is conditional formatting. I have five different colours (including no fill) that the formatting needs to paint automatically across three cells in a row.

For example data in D5:F5, I need it to autoformat fill colour in blue if it detects the same value (a name) in D5 and highlight all three cells on both records. I need it to fill colour orange if it detects the value 'none' from a dropdown in E5 and 'Y' in a dropdown in F5, but colour it red if it detects 'none' in E5 but 'N' (none) in F5. Also data in any row, that detects a 'N' (none) in F5 and any value other than 'none' in E5, needs to be filled in a green colour, again spanning the three cells.

All Ive been able to do so far is colour one cell dependant upon the value in E5. I need it to be more advanced though. Also I need this to be automatic so if extra data is inserted or altered in the future, it will automatically do the conditional formatting itself.



Thanks for reading, comments and help would be great ....

[wh666-666]

Bumpy bumpy .... Could really do with help ASAP ....

(also on a sidenote, its office 2003 and viewed on 2k and 2003)

[Adisharr]

I'm sure you could do this is VBA - I'm not a regular user of that though. I'll see if I can come up with something.

[Adisharr]

Hi sharkies ..


- Second problem is conditional formatting. I have five different colours (including no fill) that the formatting needs to paint automatically across three cells in a row.

For example data in D5:F5, I need it to autoformat fill colour in blue if it detects the same value (a name) in D5 and highlight all three cells on both records. I need it to fill colour orange if it detects the value 'none' from a dropdown in E5 and 'Y' in a dropdown in F5, but colour it red if it detects 'none' in E5 but 'N' (none) in F5. Also data in any row, that detects a 'N' (none) in F5 and any value other than 'none' in E5, needs to be filled in a green colour, again spanning the three cells.

Could you clarify '..I need it to autoformat fill colour in blue if it detects the same value (a name) in D5..

Same value as what?

[wh666-666]

I thought of creating a program in visual basic, but ideally id prefer a spreadsheet as they're easier to email through firewalls. If it were an exe it would need to be below 5MB. The other thing about spreadsheets is they can be integrated in to other reports.

The first worksheet needs to have additional data attached in a section every fortnight, the second one would need to be edited less ...

It might help if I explain some details, just because of this companies data protection, I have to be careful with what I say since I had to sign an advanced confidentiality agreement.

Anyway the first sheet is a fortnightly report on how many times an autoping fails at a specified device at specified locations ..

The second worksheet contains referencing details. For example it says that boston has none, or one or several devices and whether staff are at that location.

So for example:

record one> D5: Boston - E:5 device name 1 - F:5 N (as in no staff at that location)

record two> D5: Newhaven - E:5 device name 1 - F:5 Y
record two> D5: Newhaven - E:5 device name 2 - F:5 Y


So record one would have an autofill between D:5 > E:5 of green since there is no staff at that location. If it did have staff, it would have no autofill colour.

But lets say record one had no device but staff, it would be autofilled in orange. If it had no staff and no device, it would be red.

Then record two as an example, each device at a location has a record in a row. If there two devices at a location for example, then the record will be will be listed twice. These two rows of records would be coloured blue to distinguish its the same location, but multiple devices.


I dont know it that makes it any clearer? Im just looking to make this so that even when im not involved in using the document, it will do everything automatically for the technically illiterate ...


Am i asking for too much from excel 2003? Is there a better way to do this? I did consider access, but the problem is creating simple transferable reports and again ease of use for the user.

[Adisharr]

Thanks for the additional info. WH. VBA (Visual Basic for Applications) is built into Excel so that could be used. I've used it a few times in a rudimentary way to do some custom sorting and to show or hide rows based on contents.

I'll take a look at this tonight and see if I can come up with anything to help.