I have a workbook with a worksheet for each month of the year plus a summary sheet for the whole year. Each monthly worksheet has 400 to 600 rows each representing a transaction/sale. I sort the rows based on who provided the service and calculate sales and other data for each provider. I summarize various results in a small table to be used by (referenced by) the summary worksheet.
Each month, after sorting the rows by provider, I visually find the start and end rows for the transactions for each provider and record them in a two column table, see example for month of February:
Mary Start Row 2
Mary End Row 135
John Start Row 136
John End Row 280
Albert Start Row 281
Albert End Row 490
I would like to do some math, a summation, for example, on ranges named FebMary which would be G2:G135, FebJohn which would be G136:G280, and FebAlbert which would be G281:G490.
When I populate February worksheet and complete the small table, I would like my three range names to be automatically (or semi-automatically) defined for existing range names FebMary, FebJohn, and FebAlbert. I would like them to update if I change the row numbers in the table or at least to be updateable with some action--a macro perhaps? A possible formula using the range names would be =sum(FebMary).
Alternately, instead of starting and ending row numbers in the second column, I could populate the small table with cell addresses which would be for Mary G2 and G135 etc. In which case, how would I use this in a formula in another cell, such as =sum(G2:G135) without inputting the start and end cells by hand?
Or is this a place to use arrays? And how would I do that?
Thanks for any help,
