# Formula that references another formula to return results

#### hgufrin

##### Board Regular
In cell A1, I have the simple formula =Sum(A12:A16)
In cell B1 I have =SUM(B12:B16).

Tomorrow I might modify my formula in cell A1 to be =Sum (A17:A54); obviously, B1 will have =Sum(B17:B54)

How can I make it so that cell B1's formula reads A1's formula and knows to change the range automatically? Is there some sort of offset formula or something?

The problem is that I have 30 columns worth of information with the sum formula in row 1. I do not like updating every column each morning as the data ranges change from day to day.

I do appreciate your help.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi hgufrin

One possibility is instead of harcoding the ranges in the formulas use a named range. This way each day you just change the named range and the formulas will adjust.

Hope this helps
PGC

Try using Named ranges. Here is how:

Highlight A12:A16, then go to Insert | Name | Define and give this range a name, like MyRange.

So, you can get the SUM of A12:A16 by using:
=SUM(MyRange)

You can then get the SUM of B12:B16 by using:
=SUM(OFFSET(MyRange,0,1))

So, the next time your row range changes, simply reset MyRange, and all formulas which use it will change reflect the new range.

Try:

=SUM(INDEX(A:A,\$F\$1):INDEX(A:A,\$G\$1))

where F1 contains your start Row #, eg. 17 and G1 contains your end row #, eg. 54.

Then all you do is change these 2 numbers.

The formula can be dragged to the right to change to accomodate column B, C, etc....

jm14, pgc01, Nbvc,

i understand what you did but it doesn't work for my application.

Any one else? to recap... i have at least 30 formulas in row 1. Cells A1, B1, C1, etc.

When i change the range in A1 from lets say A15:A50 then i would like cells B1, C1, etc to change automatically to B15:B50, C15:C50.. (ie- it is reading the formula in cell A1 and changing its range accordingly)

I sure do hope some one can help out on this.

jm14, pgc01, Nbvc,

i understand what you did but it doesn't work for my application.

Any one else? to recap... i have at least 30 formulas in row 1. Cells A1, B1, C1, etc.

When i change the range in A1 from lets say A15:A50 then i would like cells B1, C1, etc to change automatically to B15:B50, C15:C50.. (ie- it is reading the formula in cell A1 and changing its range accordingly)

I sure do hope some one can help out on this.

Why don't they work for you app?

With my suggestion, all you need to do is assign 2 cells anywhere on your sheet to house the start and end rows. Then the formula (dragged across row 1) would auto-update according to any changes in those 2 pre-assigned cells.

Likewise, I do not understand why my solution will not work for your app.

Once you have set up the formulas, all you need to do is change the named range (one step), and all the formulas will automatically reflect this without you having to do anything else.

As a matter of fact, you can even simply the formula so that you only have to use one formula that you can copy to all columns that will sum that column, i.e.

=SUM(OFFSET(MyRange,0,COLUMN()-1))
this will SUM whatever column the formula is found in, based on the rows you have selected when naming MyRange.

Alright,

on a day to day basis i go to the internet and pull up an excel file that some company i work with updates every night. I some of the data off it.

I cant use a named range because i dont know exactly how the information will be displayed or where on a sheet the information is going to be (i never know where the start and end rows are going to be). They change it all the time.

Can you think of anything else that might help? I DO appreciate your time.

In order to develop a formula that will work, we need to know at least some basic ground rules.

Where might the data reside?
Are there maximum or minimum rows?
What is the layout?

Is there anything else on the sheet?
If not, can't you just sum the whole column?, i.e.
=SUM(A2:A65536)

Replies
3
Views
122
Replies
9
Views
226
Replies
0
Views
58
Replies
7
Views
253
Replies
3
Views
45

Threads
1,216,487
Messages
6,130,945
Members
449,608
Latest member
jacobmudombe

### 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

### 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