Mixed cell reference

  • Thread starter Thread starter Bill
  • Start date Start date
B

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.
 
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.
 
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.
 
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.
 
OK. Try the below

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

If this post helps click Yes
 
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.
 
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.
 
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.
 
Back
Top