S
SkippyPB
I'm hoping someone here can set me on the right path via an example so
that I can complete this onerous task.
Currently I have a spreadsheet for fantasy football that I maintain.
The Workbook (Excel 2003) has the following worksheets
Sheet 1 = Named Totals
Sheets 2 - 18 = Named Week1, Week2, Week3, etc to Week17
Sheet19 = Status
Sheet 20 = Roster
Roster has 5 columns, 1 for each player position (QB, RB, WR, K, Def)
Under each column is listed the person or team for that slot.
Status has 18 columns and 44 rows. Some rows are blank or just
contain a description. Column 1 contains a player position. For
example, in column rows 1-4 are the 4 QB names (from sheet Roster);
rows 9 -17 contain the RB names from Roster etc.
Columns 2 thru 18 are for Week1, Week2, Week3 etc. thru Week 17.
In Rows 2 thru 18 there would be either a Y or N indicating if the
player listed in column 1 of that row is playing that week or not.
Sheets 2 - 18 have the same set up in column 1 as sheet Status.
However, columns 2 - 29 contain different categories of data that are
tracked for each player. For example, column 1 is for rushing yards,
column 2 is for rushing TDs, column 3 is for receiving yards etc. etc.
Sheet Totals, column 1 has the same set up as Status and Sheets 2 -
18. Column 2 relates to week 1, column 3 week 2, column 4 week 3 etc.
etc.
Therefore in Row 4, column 1 there is a formula to calculate the total
for QB1 week 1. Row 5, column 1 has the formula to calculate the
total for QB2 week 1 etc. etc. When all is said and done, the Totals
sheet contains 476 formulas to cover all positions for all 17 weeks.
So when there is a change in a formula, it is a real pain to go in and
change all of them. There are 5 distinct formulas among the 476. That
is, the Workbook allows for QB1-QB4 so all formulas for them are the
same with the only difference being the row they look at for the data.
In addition it allows RB1-RB9 and that is a distinct formula from the
QB formula.
My goal is to change as much of the formula to a function so that in
the future it will be easier to maintain the calculations and easier
to add more personnel in a category. I apologize for having such a
long winded explanation, but I thought it important in understanding
what I'm trying to accomplish.
Here is the formula that sits in Column B, Row 4 (QB1, Week 1):
=IF(Week1!$B3="y",(TRUNC((Week1!$C3/10))+(Week1!$D3*6)+(TRUNC(Week1!$E3/10))+(Week1!$F3*6)+(TRUNC(Week1!$G3/20))+(Week1!$H3*3)+(Week1!$I3*2)+(Week1!$J3*2)+(Week1!$K3*1)),0)
Here is the formula that sits in Column B, Row 5 (QB2, Week 1):
=IF(Week1!$B4="y",(TRUNC((Week1!$C4/10))+(Week1!$D4*6)+(TRUNC(Week1!$E4/10))+(Week1!$F4*6)+(TRUNC(Week1!$G4/20))+(Week1!$H4*3)+(Week1!$I4*2)+(Week1!$J4*2)+(Week1!$K4*1)),0)
Here is the formula that sits in Column C, Row 4 (QB1, Week 2):
=IF(Week2!$B3="y",(TRUNC((Week2!$C3/10))+(Week2!$D3*6)+(TRUNC(Week2!$E3/10))+(Week2!$F3*6)+(TRUNC(Week2!$G3/20))+(Week2!$H3*3)+(Week2!$I3*2)+(Week2!$J3*2)+(Week2!$K3*1)),0)
And finally, by way of showing the differences and usage of all of the
sheets and columns, here is the formula that sits in Column C, Row 5
=IF(Week2!$B4="y",(TRUNC((Week2!$C4/10))+(Week2!$D4*6)+(TRUNC(Week2!$E4/10))+(Week2!$F4*6)+(TRUNC(Week2!$G4/20))+(Week2!$H4*3)+(Week2!$I4*2)+(Week2!$J4*2)+(Week2!$K4*1)),0)
If someone can suggest a way to turn those formulas into functions
then I can apply that technique to the other categories. So far, I
have been unable come up with anything that actually works.
Thank you in advance for any help you can offer.
////
(o o)
-oOO--(_)--OOo-
Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and never amounted to much.
The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Remove nospam to email me.
Steve
that I can complete this onerous task.
Currently I have a spreadsheet for fantasy football that I maintain.
The Workbook (Excel 2003) has the following worksheets
Sheet 1 = Named Totals
Sheets 2 - 18 = Named Week1, Week2, Week3, etc to Week17
Sheet19 = Status
Sheet 20 = Roster
Roster has 5 columns, 1 for each player position (QB, RB, WR, K, Def)
Under each column is listed the person or team for that slot.
Status has 18 columns and 44 rows. Some rows are blank or just
contain a description. Column 1 contains a player position. For
example, in column rows 1-4 are the 4 QB names (from sheet Roster);
rows 9 -17 contain the RB names from Roster etc.
Columns 2 thru 18 are for Week1, Week2, Week3 etc. thru Week 17.
In Rows 2 thru 18 there would be either a Y or N indicating if the
player listed in column 1 of that row is playing that week or not.
Sheets 2 - 18 have the same set up in column 1 as sheet Status.
However, columns 2 - 29 contain different categories of data that are
tracked for each player. For example, column 1 is for rushing yards,
column 2 is for rushing TDs, column 3 is for receiving yards etc. etc.
Sheet Totals, column 1 has the same set up as Status and Sheets 2 -
18. Column 2 relates to week 1, column 3 week 2, column 4 week 3 etc.
etc.
Therefore in Row 4, column 1 there is a formula to calculate the total
for QB1 week 1. Row 5, column 1 has the formula to calculate the
total for QB2 week 1 etc. etc. When all is said and done, the Totals
sheet contains 476 formulas to cover all positions for all 17 weeks.
So when there is a change in a formula, it is a real pain to go in and
change all of them. There are 5 distinct formulas among the 476. That
is, the Workbook allows for QB1-QB4 so all formulas for them are the
same with the only difference being the row they look at for the data.
In addition it allows RB1-RB9 and that is a distinct formula from the
QB formula.
My goal is to change as much of the formula to a function so that in
the future it will be easier to maintain the calculations and easier
to add more personnel in a category. I apologize for having such a
long winded explanation, but I thought it important in understanding
what I'm trying to accomplish.
Here is the formula that sits in Column B, Row 4 (QB1, Week 1):
=IF(Week1!$B3="y",(TRUNC((Week1!$C3/10))+(Week1!$D3*6)+(TRUNC(Week1!$E3/10))+(Week1!$F3*6)+(TRUNC(Week1!$G3/20))+(Week1!$H3*3)+(Week1!$I3*2)+(Week1!$J3*2)+(Week1!$K3*1)),0)
Here is the formula that sits in Column B, Row 5 (QB2, Week 1):
=IF(Week1!$B4="y",(TRUNC((Week1!$C4/10))+(Week1!$D4*6)+(TRUNC(Week1!$E4/10))+(Week1!$F4*6)+(TRUNC(Week1!$G4/20))+(Week1!$H4*3)+(Week1!$I4*2)+(Week1!$J4*2)+(Week1!$K4*1)),0)
Here is the formula that sits in Column C, Row 4 (QB1, Week 2):
=IF(Week2!$B3="y",(TRUNC((Week2!$C3/10))+(Week2!$D3*6)+(TRUNC(Week2!$E3/10))+(Week2!$F3*6)+(TRUNC(Week2!$G3/20))+(Week2!$H3*3)+(Week2!$I3*2)+(Week2!$J3*2)+(Week2!$K3*1)),0)
And finally, by way of showing the differences and usage of all of the
sheets and columns, here is the formula that sits in Column C, Row 5
=IF(Week2!$B4="y",(TRUNC((Week2!$C4/10))+(Week2!$D4*6)+(TRUNC(Week2!$E4/10))+(Week2!$F4*6)+(TRUNC(Week2!$G4/20))+(Week2!$H4*3)+(Week2!$I4*2)+(Week2!$J4*2)+(Week2!$K4*1)),0)
If someone can suggest a way to turn those formulas into functions
then I can apply that technique to the other categories. So far, I
have been unable come up with anything that actually works.
Thank you in advance for any help you can offer.
////
(o o)
-oOO--(_)--OOo-
Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and never amounted to much.
The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Remove nospam to email me.
Steve