R
radiantmoon
Hi everyone
I've been mulling over this for a few weeks now without luck! I
could've probably done it the long way ten times now, but i'm
determined to get this spreadsheet working how i want it!! Any help
will be appreciated.
Here's the situation...
I've got a workbook working out salaries for the year.
Sheet 1 has the breakdown for everybody, for every day of the year,
totalled fortnightly.
Sheet 2 : Sheet 13 is Jan : Dec
All these sheets are laid out identically.
There are 21 employees - Row 4 : 24 for the first fortnight and row 28
: 48 for the second fortnight.
Column A lists the employee name, Column B : F has details about the
salary.
Column B and C takes info from Sheet 1.
Column D, E and F has various formulas.
Column G is a Total of C : F
Likewise Row 25 and 49 are totals for the fortnight
And Row 51 is a total of 25 and 49.
Row 2 and 27 has what fortnight it is (eg January 1 - 15)
Hopefully this is all making sense so far....
Sheet 14 : Sheet 34 is each labelled with the employee's name.
These sheets are also laid out identically.
Column A starts "January 1 -15" and ends "December 16 - 31" (taking up
Rows 4 : 27 respectively)
Columns B : F has the same details about salary as Sheets 2 : 13.
What i want to do is take whatever results i get from Sheets 2 : 13 and
have the full yearly summary on the employee's individual sheets.
Eg, for employee name John, i want to display what his salary was in
January 1 - 15, January 16 - 31, February 1 - 15, February 16 - 29,
right through to December 16 - 31.
The long way i used to do this was to go to every month, copy the data
for the employee, go the the employee's sheet, and paste the
link.....needless to say this is very loooong and boring!
Is there a formula that can simplify the process so i can just use the
same formula on every sheet.
I tried
=OFFSET(Jan!$A$2,MATCH($A$5,Jan!$A$2:$A$48,0),MATCH($A$1,Jan!$A$4:$G$4,0))
but then i would still have to change the name of the sheet for every
month, and i could only get it to work properly on one line!!
I would keep fiddling, but i want to start using this spreadsheet from
the beginning of 2004 and its getting a bit frustrating!!
HELP ME PLEASE!
Moons
I've been mulling over this for a few weeks now without luck! I
could've probably done it the long way ten times now, but i'm
determined to get this spreadsheet working how i want it!! Any help
will be appreciated.
Here's the situation...
I've got a workbook working out salaries for the year.
Sheet 1 has the breakdown for everybody, for every day of the year,
totalled fortnightly.
Sheet 2 : Sheet 13 is Jan : Dec
All these sheets are laid out identically.
There are 21 employees - Row 4 : 24 for the first fortnight and row 28
: 48 for the second fortnight.
Column A lists the employee name, Column B : F has details about the
salary.
Column B and C takes info from Sheet 1.
Column D, E and F has various formulas.
Column G is a Total of C : F
Likewise Row 25 and 49 are totals for the fortnight
And Row 51 is a total of 25 and 49.
Row 2 and 27 has what fortnight it is (eg January 1 - 15)
Hopefully this is all making sense so far....
Sheet 14 : Sheet 34 is each labelled with the employee's name.
These sheets are also laid out identically.
Column A starts "January 1 -15" and ends "December 16 - 31" (taking up
Rows 4 : 27 respectively)
Columns B : F has the same details about salary as Sheets 2 : 13.
What i want to do is take whatever results i get from Sheets 2 : 13 and
have the full yearly summary on the employee's individual sheets.
Eg, for employee name John, i want to display what his salary was in
January 1 - 15, January 16 - 31, February 1 - 15, February 16 - 29,
right through to December 16 - 31.
The long way i used to do this was to go to every month, copy the data
for the employee, go the the employee's sheet, and paste the
link.....needless to say this is very loooong and boring!
Is there a formula that can simplify the process so i can just use the
same formula on every sheet.
I tried
=OFFSET(Jan!$A$2,MATCH($A$5,Jan!$A$2:$A$48,0),MATCH($A$1,Jan!$A$4:$G$4,0))
but then i would still have to change the name of the sheet for every
month, and i could only get it to work properly on one line!!
I would keep fiddling, but i want to start using this spreadsheet from
the beginning of 2004 and its getting a bit frustrating!!
HELP ME PLEASE!
Moons