I've got a spreadsheet here of employee schedules. Each column represents an
employee, and each row a date. A typical column would look like:

Name
off
off
off
---start
work
work
work
work
work
---end
off
off
off

Looking for a tip for the best way to change the start and end rows and have the
rest update. I know there's a scripting language, but I'm looking for a solution
that won't scare others away. The current copy-paste method being used is
producing a lot of errors.

Post a reply to this message

On 06/03/2012 1:04 PM, Shay wrote:
> Looking for a tip for the best way to change the start and end rows and have the
> rest update. I know there's a scripting language, but I'm looking for a solution
> that won't scare others away. The current copy-paste method being used is
> producing a lot of errors.

Do you mean put the end rows where the start rows are?
If so select the rows, cut the go to the first start row. Right click 
and insert cut cells.

If that is not what you mean, please explain further.

-- 
Regards
     Stephen

Post a reply to this message

"Shay" <nomail@nomail> wrote:
> I've got a spreadsheet here of employee schedules. Each column represents an
> employee, and each row a date. A typical column would look like:
>
> Name
> off
> off
> off
> ---start
> work
> work
> work
> work
> work
> ---end
> off
> off
> off
>
> Looking for a tip for the best way to change the start and end rows and have the
> rest update. I know there's a scripting language, but I'm looking for a solution
> that won't scare others away. The current copy-paste method being used is
> producing a lot of errors.

You can do something like that:

=If(OR(C4="";C4="end");"off";IF(OR(C4="start";C4="work");"work";"off"))
Put that in cell C5 (that's where I started) and copy it wherever you need it

It will fill out all cells with "off" if the cell above is anything but "start"
or "work", and anything after "end" is "off" again.

Regards
Aydan

Post a reply to this message

>You can do something like that:
 >
 >=If(OR(C4="";C4="end");"off";IF(OR(C4="start";C4="work");"work";"off"))
 >Put that in cell C5 (that's where I started) and copy it wherever you 
 >need it
 >
 >It will fill out all cells with "off" if the cell above is anything 
 >but "start"
 >or "work", and anything after "end" is "off" again.
 >

Similar effect would be with this formula in the cell C5:
   =if((C4<>"---start")*(C4<>"work");"off";"work")
or, if the list separator is not";" but "," :
   =if((C4<>"---start")*(C4<>"work"),"off","work")

Post a reply to this message