Mixed cell reference

[Bill]

Would like to name a specific column BUT allow the row to change.

Example: columns named "MP1" and "MP2" with intervening columns; rows 1 to 3

MP1 ... MP2 ANSWER Formula

1 21 22 23 SUM(MP1 1:MP2 1)
2 5 6 7 SUM(MP1 2:MP2 2)
3 0 2 9 SUM(MP1 3:MP2 3)

The SUM formulas above are not valiid, but I hope the reader can suggest
formulas that are valid.

Reason: Next year the locations of MP1 and MP2 might change but the rows
will not and I would like to simply delete the current names MP1 and MP2 and
name some new columns MP1 and MP2 without changing the formulas.

 

[Jacob Skaria]

Enter the below formula and copy that down

=mp1+mp2


If this post helps click Yes

 

[Don Guillett]

I can't visualize your project.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[RagDyeR]

Try this:

=SUM(INDEX(MP1,ROWS($1:1)):INDEX(MP2,ROWS($1:1)))

Careful how you rename your ranges!
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Would like to name a specific column BUT allow the row to change.

Example: columns named "MP1" and "MP2" with intervening columns; rows 1 to
3

MP1 ... MP2 ANSWER Formula

1 21 22 23 SUM(MP1 1:MP2 1)
2 5 6 7 SUM(MP1 2:MP2 2)
3 0 2 9 SUM(MP1 3:MP2 3)

The SUM formulas above are not valiid, but I hope the reader can suggest
formulas that are valid.

Reason: Next year the locations of MP1 and MP2 might change but the rows
will not and I would like to simply delete the current names MP1 and MP2 and
name some new columns MP1 and MP2 without changing the formulas.

 

[Bill]

Thanks, but your suggestion will only add contents of MP1 to MP2.
Intervening coulumns will not be considered. If I use SUM(MP1:MP2) then
content of all rows and columns from MP1 to MP2 will be processed.

 

[Jacob Skaria]

OK. Try the below

=SUM(INDEX(col1,ROW()):INDEX(col2,ROW()))

If this post helps click Yes

 

[Bill]

Thank you so much! I did not get the exact results from your formula, but
modified it to =SUM(INDEX(MP1,ROW()):INDEX(MP2,ROW())). Your formula yielded
the correct results for the row immediately BELOW the row in which the
formula appeared.

 

[Ragdyer]

The arguments in the Index function are *relative*, meaning the first row
pertains to the first row of the *indexed range*, not necessarily the row
number of the sheet.

Your example shows Row1 as the start of MP1 and MP2 *and* the start of the
data.

If you're including headers in your named range, you can easily adjust the
formula to start on the 2nd row of the range:
Rows($1:2),
Or the 3rd row of the range:
Rows($1:3)

The Rows() function is a more robust choice over the Row() function when the
possibility of inserting additional rows may come into play.

Insert a new row 1 & 2 and see what happens to your formula when using the
Row() function.

 

[Bill]

Thanks again and thanks to Jacob for his solution. I have been looking for a
way to do this for a fairly long time. I did try the INDEX function once but
could not get it to work the way that you did.