Copying cell formats

[Guest]

Hi,
I am working with large spreadsheets for staff rostering. The main planner
sheet holds info for each staff member acros the columns, and the dates they
are working down the rows. Each staff/date cell ahs a time value (the shift
start time) or a text value (Leave, sick, etc). For the non-shift times ,the
cells are highlighted manually.
I also have a summary sheet, which displays ony 4 weeks at a time, by using
the VLOOKUP formula to select the date's values ,based on a given date on the
summary page.

What I also want to do is copy the cell fill colour from the planner sheet
to the summary sheet - any help would be appreciated.

Stu

 

[Guest]

Try...
Edit>Paste Special>Formats
HTH,

 

[Guest]

Hi Gary,

Thanks for that, but I probably missed out the word "automatically".

Each time I reset the date on the summary page, the VLOOKUP copies all the
shift times automatically (as Excel does) to the summary page. What I need to
occur at the same time is the automatic update of the fill colour for each
cell. I accept that if I was manually copying the planner details to the
summary sheet, then paste with formats would be the option to use.

The other challenge I have is that these workbooks (there are four, 1 for
each location) are also updated by non-pc literate staff, so it needs to be
as simple to operate as possible.

Stu

 

[wmjenner]

What determines the cell color? Is it just colored to distinguish one
cell (or person) from another? If the cell color is tied to some
value, you could use conditional formatting on the target page and,
depending on the criteria for the cell color, it would change
automatically as the criteria change. A little more detail on what
you're trying to accomplish will probably yield more targeted
responses.

 

[Guest]

Hi,

What determines the colour originally is the non-timed shift - eg Annual
Leave (yellow), sick(blue), or what's called Relief(green), which are
un-allocated shifts, which are dependent on other staff absence requirements.

E.g. a staff memeber might have a week of Relief days, which are coloured
green, but then might have to work a couple of timed shifts, 08:00, 16:00,
during that period. What happens with conditional formatiing is that the
Relief (green) cell becomes 08:00 (white) - so I lose that fact that the cell
was/is a relief shift, not a normal rostered one.

As the cells are manually coloured on the planner sheet, this reset of the
cell colour does not occur, but the summary sheet should only be a
re-designed copy, and is therefore not actually updated manually, only be
reference to the planner sheet.

If that makes any sense at all...

 

[wmjenner]

I sort of get it but not quite enough to give an intelligent answer. If
you'd like, you can send the master and summary sheets (or a piece from
each) and I'll take a look at it. You should be able to use a formula
in the summary sheet to maintain the colored cell regardless, but
without a clearer picture of the different parameters, it's a little
hard to do without seeing it.

(e-mail address removed)