Changing ranges in formulas on the fly

RandyTampa

New Member
Joined
Dec 18, 2003
Messages
45
Sorry if this is vague but this is essentially my problem:

I have several sets of zip codes across a state. I want to take groups of zip codes at a time and apply formulas to all the zip codes in a group at a time. The issue is that not all groups have the same number of zip codes.

For example, group A has 100 zip codes and group B has 74 zip codes.
Formulas might involve summing a column C so that in that column
=sum(D1:D$101) for Group A but for goup B that formula would be sum(D1:D$75).

Since there are dozens of formulas that are dependent upon a given number of rows across many different tabs, it's not feasible to make individual formula changes for each group.

Any advice?
thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
As far as your summation formula goes, as long as the non-used cells are empty, you can sum ALL the rosw. Thus, if column D in Sheet1 has valid entries in the range D1:D101, and, on Sheet2, the range is only D1:D75, the formula you show, =sum(D1:D$101), would work equally well. Just use a number equal to, or higher than, the largest row you expect.

By the way, your summation formula, I believe, could be written simply as: =sum(D1:D101), without the absolute symbol $. Just enter it in the same cell on every sheet, say in cell D102, for 101 being the lowest row.

Note that, if you put the summation formula in the first row, with the data range beginning in the second row, your formula could then be: =sum(D2:D102), and you would have the sum always in the same row, row 1.
 

RandyTampa

New Member
Joined
Dec 18, 2003
Messages
45
Thanks

Thanks both of you. I wound up using an indirect reference to the number of rows to be counted/calculated for each formula. I will refer back to what you have posted in case I run into to problmes later.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
It was nice to hear that you were able to solve your problem yourself, using a different approach. There are usually various solutions possible, and it is up to the user to select one that works for him, as in your case. Congratualtions!
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
It was nice to hear that you were able to solve your problem yourself, using a different approach. There are usually various solutions possible, and it is up to the user to select one that works for him, as in your case. Congratulations!
 

Forum statistics

Threads
1,136,926
Messages
5,678,605
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top