Efficient formula repost

[David Heaton]

Hi,

I hope the format for this post works

I'm new to this newsgroup and newsgroups in general and I hope its ok to add
snapshots to posts....


I created a sheet a while back part of which showed a monthly snapshot of
when staff were working. The issue I had was that staff worked on a
rotating 4 week roster not a monthly roster and it took me quite some time
to get it right.

As I said I've only just recently started using this newsgroup and have seen
some very advanced and inventive solutions to problems. As such my question
is this. Could I have achieved the results below using a more efficient
formula or method.



The FortNightRoster sheet contained the rotating fortnightly roster for the
staff ... I needed to repeat week 1 in order to get the transposition to
work...

WEEK 1 WEEK 2 WEEK 3 WEEK 4
NAME Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
Employee Name x x x x x x x x x x x x x x x x x x x x x x x x x




below is the CurrentRoster sheet which ended up like this ( the first col is the first day of whichever month we are in (1/11/07).


NAME Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Employee Name x x x x x x x x x x x x x x x x x x x x x x



I used the formula
=IF(SUMPRODUCT(((Holiday_Name=$A4)*(HolidayStart<=B$1)*(HolidayEnd>=B$1)))>0,"h",IF((COLUMN(B4)>rostercol),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)-rostercol,1,1),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)+28-rostercol,1,1)))

in all cells in the CurrentRoster sheet.

The NAME cells on both sheets are at A3.

The SUMPRODUCT part was just a reference to a range containing holiday datesand can be ignored for this purpose.

ROSTERCOL was just a static reference to the column the roster started on,in this case 2

I hope i have explained this ok.

thanks in advance


David Heaton

 

[Roger Govier]

Hi David

I would insert a new row 1 in your CurrentRoster sheet.
In B1 I would enter the date for the start of the month 01/11/2007
In B2 enter the formula
=TEXT($B$1+COLUMN()-2,"ddd")
and copy through to AG2
in A1 of the sheet enter
=MOD(WEEKNUM(B1),4)*7

Note that Weeknum needs the Analysis Toolpak to be loaded Tools>Addins>Analysis Toolpak
When you want a new Month, just alter the date in B1

In cell A3 enter
=INDEX(Sheet1!$A$2:$AJ$11,MATCH(Sheet2!$A3,Sheet1!$A$2:$A$11,0),
MATCH(Sheet2!B$2,Sheet1!$A$2:$AJ$2,0)+Sheet2!$A$1+7*((INT((COLUMN()+1)/7))))
and copy across to AG3

On your Fortnight Roster sheet, you have a extra space after Mon. This needs to be removed otherwise all Mondays show up with an error. You will also need to enter Mon through Sun in cells AD2:AJ2 of the sheet.


--
Regards
Roger Govier



Hi,

I hope the format for this post works

I'm new to this newsgroup and newsgroups in general and I hope its ok to add
snapshots to posts....


I created a sheet a while back part of which showed a monthly snapshot of
when staff were working. The issue I had was that staff worked on a
rotating 4 week roster not a monthly roster and it took me quite some time
to get it right.

As I said I've only just recently started using this newsgroup and have seen
some very advanced and inventive solutions to problems. As such my question
is this. Could I have achieved the results below using a more efficient
formula or method.



The FortNightRoster sheet contained the rotating fortnightly roster for the
staff ... I needed to repeat week 1 in order to get the transposition to
work...

WEEK 1 WEEK 2 WEEK 3 WEEK 4
NAME Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
Employee Name x x x x x x x x x x x x x x x x x x x x x x x x x




below is the CurrentRoster sheet which ended up like this ( the first col is the first day of whichever month we are in (1/11/07).


NAME Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Employee Name x x x x x x x x x x x x x x x x x x x x x x



I used the formula
=IF(SUMPRODUCT(((Holiday_Name=$A4)*(HolidayStart<=B$1)*(HolidayEnd>=B$1)))>0,"h",IF((COLUMN(B4)>rostercol),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)-rostercol,1,1),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)+28-rostercol,1,1)))

in all cells in the CurrentRoster sheet.

The NAME cells on both sheets are at A3.

The SUMPRODUCT part was just a reference to a range containing holiday datesand can be ignored for this purpose.

ROSTERCOL was just a static reference to the column the roster started on,in this case 2

I hope i have explained this ok.

thanks in advance


David Heaton

 

[David Heaton]

Thanks Roger.

I still have one question though. I notice you use Index & Match inplace of Offset an approach that seems quite common.

Are there any advantages in this?

Regards

David
"Roger Govier" <rogerattechnology4NOSPAMu.co.uk> wrote in message Hi David

I would insert a new row 1 in your CurrentRoster sheet.
In B1 I would enter the date for the start of the month 01/11/2007
In B2 enter the formula
=TEXT($B$1+COLUMN()-2,"ddd")
and copy through to AG2
in A1 of the sheet enter
=MOD(WEEKNUM(B1),4)*7

Note that Weeknum needs the Analysis Toolpak to be loaded Tools>Addins>Analysis Toolpak
When you want a new Month, just alter the date in B1

In cell A3 enter
=INDEX(Sheet1!$A$2:$AJ$11,MATCH(Sheet2!$A3,Sheet1!$A$2:$A$11,0),
MATCH(Sheet2!B$2,Sheet1!$A$2:$AJ$2,0)+Sheet2!$A$1+7*((INT((COLUMN()+1)/7))))
and copy across to AG3

On your Fortnight Roster sheet, you have a extra space after Mon. This needs to be removed otherwise all Mondays show up with an error. You will also need to enter Mon through Sun in cells AD2:AJ2 of the sheet.


--
Regards
Roger Govier



Hi,

I hope the format for this post works

I'm new to this newsgroup and newsgroups in general and I hope its ok to add
snapshots to posts....


I created a sheet a while back part of which showed a monthly snapshot of
when staff were working. The issue I had was that staff worked on a
rotating 4 week roster not a monthly roster and it took me quite some time
to get it right.

As I said I've only just recently started using this newsgroup and have seen
some very advanced and inventive solutions to problems. As such my question
is this. Could I have achieved the results below using a more efficient
formula or method.



The FortNightRoster sheet contained the rotating fortnightly roster for the
staff ... I needed to repeat week 1 in order to get the transposition to
work...

WEEK 1 WEEK 2 WEEK 3 WEEK 4
NAME Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
Employee Name x x x x x x x x x x x x x x x x x x x x x x x x x




below is the CurrentRoster sheet which ended up like this ( the first col is the first day of whichever month we are in (1/11/07).


NAME Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Employee Name x x x x x x x x x x x x x x x x x x x x x x



I used the formula
=IF(SUMPRODUCT(((Holiday_Name=$A4)*(HolidayStart<=B$1)*(HolidayEnd>=B$1)))>0,"h",IF((COLUMN(B4)>rostercol),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)-rostercol,1,1),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)+28-rostercol,1,1)))

in all cells in the CurrentRoster sheet.

The NAME cells on both sheets are at A3.

The SUMPRODUCT part was just a reference to a range containing holiday datesand can be ignored for this purpose.

ROSTERCOL was just a static reference to the column the roster started on,in this case 2

I hope i have explained this ok.

thanks in advance


David Heaton

 

[Roger Govier]

Hi David

Offset is one of the volatile functions. This means whenever there is any change on the worksheet, it has to be recalculated. On large sheets with lots of volatile functions, this can cause quite a slowdown in performance.

Therefore, whenever possible, I prefer to use non-volatile functions.
Other Volatile functions include
Cell()
Indirect()
Now()
Today()
Rand()
--
Regards
Roger Govier




Thanks Roger.

I still have one question though. I notice you use Index & Match inplace of Offset an approach that seems quite common.

Are there any advantages in this?

Regards

David
"Roger Govier" <rogerattechnology4NOSPAMu.co.uk> wrote in message Hi David

I would insert a new row 1 in your CurrentRoster sheet.
In B1 I would enter the date for the start of the month 01/11/2007
In B2 enter the formula
=TEXT($B$1+COLUMN()-2,"ddd")
and copy through to AG2
in A1 of the sheet enter
=MOD(WEEKNUM(B1),4)*7

Note that Weeknum needs the Analysis Toolpak to be loaded Tools>Addins>Analysis Toolpak
When you want a new Month, just alter the date in B1

In cell A3 enter
=INDEX(Sheet1!$A$2:$AJ$11,MATCH(Sheet2!$A3,Sheet1!$A$2:$A$11,0),
MATCH(Sheet2!B$2,Sheet1!$A$2:$AJ$2,0)+Sheet2!$A$1+7*((INT((COLUMN()+1)/7))))
and copy across to AG3

On your Fortnight Roster sheet, you have a extra space after Mon. This needs to be removed otherwise all Mondays show up with an error. You will also need to enter Mon through Sun in cells AD2:AJ2 of the sheet.


--
Regards
Roger Govier



Hi,

I hope the format for this post works

I'm new to this newsgroup and newsgroups in general and I hope its ok to add
snapshots to posts....


I created a sheet a while back part of which showed a monthly snapshot of
when staff were working. The issue I had was that staff worked on a
rotating 4 week roster not a monthly roster and it took me quite some time
to get it right.

As I said I've only just recently started using this newsgroup and have seen
some very advanced and inventive solutions to problems. As such my question
is this. Could I have achieved the results below using a more efficient
formula or method.



The FortNightRoster sheet contained the rotating fortnightly roster for the
staff ... I needed to repeat week 1 in order to get the transposition to
work...

WEEK 1 WEEK 2 WEEK 3 WEEK 4
NAME Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
Employee Name x x x x x x x x x x x x x x x x x x x x x x x x x




below is the CurrentRoster sheet which ended up like this ( the first col is the first day of whichever month we are in (1/11/07).


NAME Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat
Employee Name x x x x x x x x x x x x x x x x x x x x x x



I used the formula
=IF(SUMPRODUCT(((Holiday_Name=$A4)*(HolidayStart<=B$1)*(HolidayEnd>=B$1)))>0,"h",IF((COLUMN(B4)>rostercol),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)-rostercol,1,1),OFFSET(FortNightRoster!$A$3,ROW(B4)-3,COLUMN(B4)+28-rostercol,1,1)))

in all cells in the CurrentRoster sheet.

The NAME cells on both sheets are at A3.

The SUMPRODUCT part was just a reference to a range containing holiday datesand can be ignored for this purpose.

ROSTERCOL was just a static reference to the column the roster started on,in this case 2

I hope i have explained this ok.

thanks in advance


David Heaton