Read another sheet

[tex3285]

I want to do a Points For and Points Against calculation. i got the Points For to work. But now I need the points against. I want it to read the cell above or below the cell that contains the team. If that possible? See attached.

[MarvinP]

Hi tex3285,

I have an easy answer. Why not Insert a column H on all your Week sheets. Call this column PA (Points Against) and simply reverse the total scores. You could do it in a formula even. Then use the similar/same formula you created to grab PF to grab PA.

Another question. Does your formula only do a single week? I was thinking you'd like a current week total of all points scored and all points given.

[MarvinP]

Hi tex3285,

I can't leave the question without showing you how I'd do this problem. You've done a lot of work building the week tabs but I'd put all games, for all weeks on a single week tab. See Week 1 Tab for my method.

I've also created a Lookup table on the Formulas sheet so I can lookup the League each team is in. I've add a Points Against column as I suggested above.

Then for the big reason for making the data look different - Pivot Tables. I have two of them on the Records Tab. One showing the Win/Loss records and a second showing the Points For and Points Against totals.

Using the Week Filter or or League filter on the Pivot Tables you can do all your record keeping. The secret is to have all games in a single bigger table with columns of Week Number, Game Number and League included on each line.

With a pivot table the average points scored for or against is as easy to do as the total. The win percentage is also just as easy. You've worked much too hard building many sheets. A single sheet and then using Pivots would be much easier.

I hope this gives you some ideas on how a geek would do this problem. Any questions - feel free to ask.

[tex3285]

Hi tex3285,

I can't leave the question without showing you how I'd do this problem. You've done a lot of work building the week tabs but I'd put all games, for all weeks on a single week tab. See Week 1 Tab for my method.

I've also created a Lookup table on the Formulas sheet so I can lookup the League each team is in. I've add a Points Against column as I suggested above.

Then for the big reason for making the data look different - Pivot Tables. I have two of them on the Records Tab. One showing the Win/Loss records and a second showing the Points For and Points Against totals.

Using the Week Filter or or League filter on the Pivot Tables you can do all your record keeping. The secret is to have all games in a single bigger table with columns of Week Number, Game Number and League included on each line.

With a pivot table the average points scored for or against is as easy to do as the total. The win percentage is also just as easy. You've worked much too hard building many sheets. A single sheet and then using Pivots would be much easier.

I hope this gives you some ideas on how a geek would do this problem. Any questions - feel free to ask.

That works great, but I like the look I have in the orginial. But I'm going to take some things from it and use it. I'm going to use your first response and use that. Thanks for the help.

[MarvinP]

Hi tex3285,

I'm glad I could suggest something useful (like a PA column).

I admit you have a lot of work into your structure and that formula to pull from a variable worksheet is pretty amazing.

I just didn't see how you were going to accumulate multiple weeks into a single answer. This is why I went with my structure and pivot tables. If you did you could see the total or average points scored for and agains any given team, or league. You could also see if more points were being scored per week. I started to put each week's tab on a single sheet. If you were going to use my idea I was ready to chop all your week sheets into a single table. I had a method to do it but was still going to take an hour to get all sheets moved. I didn't want to spend the time if it didn't make sense to you.

Thanks for the reply and update. BTW - I stopped watching NY last night and couldn't believe they won this morning.