Excel Dynamic ranges?

%3CLINGO-SUB%20id%3D%22lingo-sub-1167567%22%20slang%3D%22en-US%22%3EExcel%20Dynamic%20ranges%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167567%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20a%20worksheet%20for%20each%20month%20of%20the%20year%20plus%20a%20summary%20sheet%20for%20the%20whole%20year.%20Each%20monthly%20worksheet%20has%20400%20to%20600%20rows%20each%20representing%20a%20transaction%2Fsale.%20I%20sort%20the%20rows%20based%20on%20who%20provided%20the%20service%20and%20calculate%20sales%20and%20other%20data%20for%20each%20provider.%20I%20summarize%20various%20results%20in%20a%20small%20table%20to%20be%20used%20by%20(referenced%20by)%20the%20summary%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20month%2C%20after%20sorting%20the%20rows%20by%20provider%2C%20I%20visually%20find%20the%20start%20and%20end%20rows%20for%20the%20transactions%20for%20each%20provider%20and%20record%20them%20in%20a%20two%20column%20table%2C%20see%20example%20for%20month%20of%20February%3A%3C%2FP%3E%3CP%3EMary%20Start%20Row%20%26nbsp%3B%202%3C%2FP%3E%3CP%3EMary%20End%20Row%20%26nbsp%3B%20135%3C%2FP%3E%3CP%3EJohn%20Start%20Row%20%26nbsp%3B%20%26nbsp%3B%20136%3C%2FP%3E%3CP%3EJohn%20End%20Row%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20280%3C%2FP%3E%3CP%3EAlbert%20Start%20Row%20%26nbsp%3B%20281%3C%2FP%3E%3CP%3EAlbert%20End%20Row%20%26nbsp%3B%20%26nbsp%3B%20490%3C%2FP%3E%3CP%3EI%20would%20like%20to%20do%20some%20math%2C%20a%20summation%2C%20for%20example%2C%20on%20ranges%20named%20FebMary%26nbsp%3B%20which%20would%20be%20G2%3AG135%2C%20FebJohn%20which%20would%20be%20G136%3AG280%2C%20and%20FebAlbert%20which%20would%20be%20G281%3AG490.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20populate%20February%20worksheet%20and%20complete%20the%20small%20table%2C%20I%20would%20like%20my%20three%20range%20names%20to%20be%20automatically%20(or%20semi-automatically)%20defined%20for%20existing%20range%20names%20FebMary%2C%20FebJohn%2C%20and%20FebAlbert.%20I%20would%20like%20them%20to%20update%20if%20I%20change%20the%20row%20numbers%20in%20the%20table%20or%20at%20least%20to%20be%20updateable%20with%20some%20action--a%20macro%20perhaps%3F%20A%20possible%20formula%20using%20the%20range%20names%20would%20be%20%3Dsum(FebMary).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternately%2C%20instead%20of%20starting%20and%20ending%20row%20numbers%20in%20the%20second%20column%2C%20I%20could%20populate%20the%20small%20table%20with%20cell%20addresses%20which%20would%20be%20for%20Mary%20G2%20and%20G135%20etc.%20In%20which%20case%2C%20how%20would%20I%20use%20this%20in%20a%20formula%20in%20another%20cell%2C%20such%20as%20%3Dsum(G2%3AG135)%20without%20inputting%20the%20start%20and%20end%20cells%20by%20hand%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20is%20this%20a%20place%20to%20use%20arrays%3F%20And%20how%20would%20I%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1167567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1167593%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Dynamic%20ranges%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167593%22%20slang%3D%22en-US%22%3EI%20will%20suggest%20you%20deploy%20PivotTable%20on%20the%20sheet%20you%20record%20your%20transaction.%20%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%20ensure%20you%20create%20helper%20column%20such%20as%20Month%20and%20Year%20from%20the%20Order_Date%20in%20the%20main%20source%20data.%20%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20addition%2C%20format%20the%20data%20using%20Excel%20Table%20(CTRL%20%2B%20T).%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1167731%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Dynamic%20ranges%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1167731%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F553721%22%20target%3D%22_blank%22%3E%40Paul_at_Valueris%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20mentioned%3C%2FP%3E%0A%3CP%3E%3CEM%3EI%20sort%20the%20rows%20based%20on%20who%20provided%20the%20service%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThat%20means%20in%20each%20row%20you%20have%20the%20field%20which%20indicates%20who%20made%20the%20transaction%2C%20correct%3F%20If%20so%20it's%20not%20clear%20why%20do%20you%20need%20Start%3AEnd%20to%20summaries.%20Or%20how%20source%20data%20looks%20like%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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,

 

2 Replies
Highlighted
I will suggest you deploy PivotTable on the sheet you record your transaction.

Also ensure you create helper column such as Month and Year from the Order_Date in the main source data.

In addition, format the data using Excel Table (CTRL + T).
Highlighted

@Paul_at_Valueris 

You mentioned

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?