I'm afraid I don't have the time to try to use the few hints you've given about your data layout to see if I can construct what your worksheet must look like.
But if looks to me like you do not even need VBA. My quick read on this is that you ought to be able to plug a couple of dynamic ranges into a SUMPRODUCT formula and get what you need.
Below is my "boilerplate" on DNR's. Search here for more.
Also search here for threads by
Aladin Akyurek, Barry Houdini or
Domenic using SUMPRODUCT and you should start to see what's possible.
<HR>
Dynamic Named Ranges
Dynamic named ranges can be used just like normal named ranges in formulas, or as sources for things like pivot tables and charts. However instead of a normal named range where the group of cells being referred to is static, dynamic ranges typically take advantage of the Offset() and CountA() [or Count()] functions to create a range that resizes to include new data appended to the bottom of the range.
To Create a Dynamic Range
- From the menu Insert | Name | Define...
- In the Names in Workbook textbox, type in the name for the Named Range - probably something like "Data" or "DataRange".
- In the Refers to textbox type in a formula like the following (note: you may find it helpful to hit the F2 key while in the textbox to put it into "edit mode"):
- =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),9) to start at A1, include all non-blank rows and 9 columns. (Good example for pivot tables where you want to include headers.)
- =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1) to start at B2, include all numeric rows and 1 column. (Good example for a chart data series.)
- =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) to start at A2, include one less than the count of all non-blank rows and 1 column. (Good example for a chart category series where values are not numeric.)
- You're ready to reference the Named Dynamic Range like you would a normal Named Range.
Karachi, seriously? Or you're in Peoria and Karachi just sounded more interesting?