"Linking" Excel workbooks

[jasonsas]

Hi,

I'm not sure if this is possible, but here goes...

We have one excel workbook which managers use to update when people are going on leave. The workbook is set out with days/months of the year across the top and peoples names going down. They highlight certain cells relating to the person and the dates they will have off and then shade them in.

A user has another "master" workbook which she would like to be automatically updated when managers update their particular workbooks. At the moment, she does this manually.

Is this possible to do?

Thanks for any help!

 

[SkipVought]

Hi,

This does not seem like a well designed system, which is often a problem with workbooks that people develop to run their business.

Using Excel built-in functionality, there is no way to detect shaded cells. Workbook cells can be linked to return the DATA VALUES in another workbook/worksheet not other cell properties.

This is the sort of thing that may be able to be done with code, via VBA, but to be able to say yea or nay, one would need to analyze the workbook structures and the business processes.

Shooting from the hip, it sounds like a veritable nightmare to me.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Gavona]

You might find it easier for all concerned if instead of shading they enter a letter or number. Perhaps tell them that if they select a range of cells type something and ctrl-enter all the cells will be filled.
If there are only one or two different coloured shadings to apply then conditional formatting will automatically shade the cells and, if you want change the colour of the text so it is invisible.
More complex shadings would need to be applied using VBA to shade the cells. Perhaps with an event or a button on the workbook to apply the shading.

Then the solution to your original problem becomes much easier. vlookup for example would work but you had better scale the problem - how many employees in total, how many different workbooks?
Does the master workbook have the same presentation - could you just automate the opening and copying of each workbook?
How will you cope when new people join or people leave...


Gavin

 

[jasonsas]

HI gavin

This problem is for another user, so I'll try to answer your questions as best I can.

Are you saying to use a letter for example, as well as shading? I'm not sure what conditional formatting is? Does this mean if they enter a certain letter or number, it will automatically format those cells?

There is one "master" workbook, and probably maximum of 3 "child" workbooks. It is only for one department, so its not that big.
I believe the master workbook has the same presentation. How would you automate the opening and copying of each workbook?

Thanks for your help!

 

[SkipVought]

That's exactly what Conditional Formatting can do. It's based on the evaluation of data.

But it would mean a fundamental CHANGE in the worksheet desidn AND by the users in the process they employ to annotate, something, I assume, that may be easier said than dome.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Gavona]

1 I assume that for presentation reason they want shading, but as Skip says there is no inbuilt way to detect a shaded cell so yes - use both.

2 Conditional formatting does as you say. You set up to 3 conditions and the format for each of the conditions (Annual Leave, Special Leave, Sick Leave?)

3 Do it manually, make a note of the steps.
Tools, Macro, RecordNewMacro....
Do it again manually
Repeat recording a new macro but this time, having started your recording, click on the relative address button (on the mini toolbar that appears) before you do your stuff
have a look at the code and work out how to generalise it

Tip: what steps can you use manually that will be identical everytime - even if the second time there are more records? Don't click on A17 because it is the first blank row then hit paste. Click on A1 and use Ctrl-Dwn to find the first blank row - or go to the very bottom of the worksheet and use ctrl-Up to get to the last record




Gavin

 

[jasonsas]

Hi,

So say we wanted to use shading (for presentation purposes), but use an invisible character for the purposes of linking workbooks.

When you say you can set up to 3 conditions, do you mean we'll only be able to have 3 types of shading, and thus, 3 types of leave (we have more than that)

When recording the macro, do you mean recording a macro to shade the cell, put in a character and make it invisible?

I'm not sure what you mean when you talk about the blank rows.

Thanks for both your help so far.

 

[Gavona]

Yes, 3 types of shading possible for any one cell (with conditional formatting). Sounds like that is ruled out.

I haven't understood if simply copying the cells from each source workbook into your masterworkbook would do the trick. If that would meet your needs then you can leave the users shading as at present.

So, in the above scenario, the Child workbooks are maintained as at present. The master is updated by opening each of the children in turn and copying the cells into the master workbook. Effectively you re-create the master each time rather then messing around tring to identify when the shading has changed. This operation shouldn't take long at all. Would it meet your needs?

(I mean't record a macro to "automate the opening and copying of each workbook" - ie the operation described above).




Gavin