Formula that references another formula to return results

[hgufrin]

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.

 

[pgc01]

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

 

[Joe4]

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.

 

[NBVC]

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

 

[hgufrin]

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.

 

[NBVC]

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.

 

[Joe4]

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.

 

[hgufrin]

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.

 

[Joe4]

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)