02-11-2020 11:12 AM
02-11-2020 11:12 AM
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,
02-11-2020 11:23 AM
02-11-2020 12:12 PM
I sort the rows based on who provided the service
That means in each row you have the field which indicates who made the transaction, correct? If so it's not clear why do you need Start:End to summaries. Or how source data looks like?