S

#### SkippyPB

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