Formula that references another formula to return results

hgufrin

Board Regular
Joined
Apr 19, 2004
Messages
172
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Apr 19, 2004
Messages
172

ADVERTISEMENT

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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Apr 19, 2004
Messages
172
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,687
Members
410,698
Latest member
Wloven
Top