Establishing a variable range based on data size

**Bjordion** · 10-05-2011, 04:00 PM

I am working with an Excel file that lists each tenant's ID# in column B. Below each tenant ID# is a list of charge amounts for that tenant in column C, one charge per row. The number of charges varies for each tenant from month to month, and the row location of each tenant's ID# also will vary from month to month.

Example: In January, tenant A ID# is in cell B4, charges are in C6 through C17. Tenant B ID# is in cell B19, charges are in C21 through C25. In February, tenant A ID# is in cell B7, charges are in C9 through C16. Tenant B ID# is in cell B18, charges are in C20 through C30.

This information is located in one tab. Ultimately I want to extract the sum of charges for each tenant and list them in another tab.

Any and all help will be greatly appreciated.

**tlafferty** · 10-05-2011, 04:05 PM

A couple of approaches could be tried. My guess is that a pivot table won't work, but we could give it a shot. If not, a macro can be developed. Can you post a sample workbook?

**ChemistB** · 10-05-2011, 04:57 PM

As per TL, a sample workbook would help immensely. If ID is in B4 and charges start in C6, what's in C4 and C5? So is column B blank between ID's? If so, the simplest solution would be to fill in Col B with the right ID's (which can be done quickly in a number of ways) and then there are lots of options open to you (depending on the rest of your data set up) like pivot tables and SUMIFs.