Bowling Fees

[sibylbanks]

Hi, I’m working on tracking what has been paid in fees by team/player based on week. Is there a program that would work to have the spreadsheet with a printout? We bowl 33 weeks. I need a printout weekly to give to the team captains showing empty for present week, last week payment, total paid and total owe.

 

[Micron]

If that's an example of what you have, post an example of what you want. I suspect the orange numbers are debt incurred but then why are there gaps? I suppose you have the balances owing and just want to know how to present a summary. For that I think you'd just create a summary sheet and calculate sums across columns (on the sheet you show) against each member and print that out. However, if you have empty cells that are supposed to represent unpaid dues, then what I see above will create false results. For that, you'd need negative numbers where the orange values are (if they're dues owing).

 

[sibylbanks]

If that's an example of what you have, post an example of what you want. I suspect the orange numbers are debt incurred but then why are there gaps? I suppose you have the balances owing and just want to know how to present a summary. For that I think you'd just create a summary sheet and calculate sums across columns (on the sheet you show) against each member and print that out. However, if you have empty cells that are supposed to represent unpaid dues, then what I see above will create false results. For that, you'd need negative numbers where the orange values are (if they're dues owing).

The orange numbers are what each person paid that week. The gaps mean they didn't pay that week. The season total is $680. When that is paid, they are paid up. We have several members who are paid up already. They may pay any amount as long as at the 30th week they have paid $680. I keep track of how much they have paid and how much they still owe. The 1st dark column indicates what they still owe. The next dark column indicates what has been paid to date. I need something that just shows each bowler's name.

..last week paid....still owe...paid to date...But I don't want the complete spreadsheet...our season goes for 33 weeks and that's too much information for the bowlers to understand (this is an older league 70 & 80 year olds with very limited computer understanding)

 

[Micron]

Is there a program that would work to have the spreadsheet with a printout?

Not sure what you're after. If it's something off the shelf or in anyone's pc I doubt it. As far as I can see, this is solvable with a helper sheet that you lay out according to how you want your printout to look. In this summary sheet you'd use sheet/cell references in formulas that get what you need from the main sheet. The tricky part may be to sum what seems like expanding ranges (because of each week needing to be added to the range). One way to deal with that could be dynamic named ranges (one range per member) or just sum the entire range of 33 weeks. There may be formulas that would deal with expanding ranges but formulas are not my thing. If the solution had to come from using vba I'd be more adept at that but I'm not really seeing a need to get that complicated.

 

[sibylbanks]

Not sure what you're after. If it's something off the shelf or in anyone's pc I doubt it. As far as I can see, this is solvable with a helper sheet that you lay out according to how you want your printout to look. In this summary sheet you'd use sheet/cell references in formulas that get what you need from the main sheet. The tricky part may be to sum what seems like expanding ranges (because of each week needing to be added to the range). One way to deal with that could be dynamic named ranges (one range per member) or just sum the entire range of 33 weeks. There may be formulas that would deal with expanding ranges but formulas are not my thing. If the solution had to come from using vba I'd be more adept at that but I'm not really seeing a need to get that complicated.

Ok,...I guess I'll keep doing double work...now I keep the spreadsheet then transfer it to a slide for each team for the printout which is redundant and prone to mistakes. But thanks anyway...Sibyl

 

[Micron]

What is wrong with what I suggested? As an example, beside my name on this summary sheet is a formula in the Total Dues Paid column:
=Sum(MainSheet!D3:AJ3 That sums across your 33 weeks on MainSheet (or whatever it's name is) if the first payment starts in col D. If you want other info beside that as well you just need to point to it with the right formula. Then you print out this summary sheet, which might only be 3 or 4 columns wide x the number of members deep. If you want, you can create one sheet per team if this is for a whole league.

 

[sibylbanks]

What is wrong with what I suggested? As an example, beside my name on this summary sheet is a formula in the Total Dues Paid column:
=Sum(MainSheet!D3:AJ3 That sums across your 33 weeks on MainSheet (or whatever it's name is) if the first payment starts in col D. If you want other info beside that as well you just need to point to it with the right formula. Then you print out this summary sheet, which might only be 3 or 4 columns wide x the number of members deep. If you want, you can create one sheet per team if this is for a whole league.

I'm sorry, I don't understand. Could you set up an example with my data above? I need a visual. Someone else recommended I do it in a Pivot table. Is that an easier way for me to understand?

 

[Micron]

I used to dabble in pivot tables but they've changed a lot since then and it has been too long. If you want to post a wb copy on a file share somewhere, I will try to do something with it, but I'm not up for starting from scratch. You can alter the names in the file to protect the innocent if you like.

 

[sibylbanks]

I used to dabble in pivot tables but they've changed a lot since then and it has been too long. If you want to post a wb copy on a file share somewhere, I will try to do something with it, but I'm not up for starting from scratch. You can alter the names in the file to protect the innocent if you like.

Thanks for giving it a go...here is the file https://docs.google.com/spreadsheets/d/19nd-iZlSjCu8I80fLhzDZiblaNjIYwiwP0EGeggj_5s/edit?usp=sharing

 

[Micron]

That's an invitation to edit a Google sheet. What I asked for was an Excel workbook. Sorry, I've never used Google sheets and don't wish to start. If that's what your post is about, maybe you should get your thread moved to somewhere other than Excel Questions?