| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mixed cell reference
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
Enter the below formula and copy that down
=mp1+mp2 If this post helps click Yes --------------- Jacob Skaria "Bill" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Bill" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
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! ================================================== === "Bill" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
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. -- Bill "Jacob Skaria" wrote: Enter the below formula and copy that down =mp1+mp2 If this post helps click Yes --------------- Jacob Skaria "Bill" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
OK. Try the below
=SUM(INDEX(col1,ROW()):INDEX(col2,ROW())) If this post helps click Yes --------------- Jacob Skaria "Bill" wrote: 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. -- Bill "Jacob Skaria" wrote: Enter the below formula and copy that down =mp1+mp2 If this post helps click Yes --------------- Jacob Skaria "Bill" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
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. -- Bill "RagDyeR" wrote: 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! ================================================== === "Bill" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bill" wrote in message ... 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. -- Bill "RagDyeR" wrote: 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! ================================================== === "Bill" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mixed cell reference
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. -- Bill "Ragdyer" wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bill" wrote in message ... 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. -- Bill "RagDyeR" wrote: 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! ================================================== === "Bill" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| mixed formatting in same cell with VBA | Excel Discussion (Misc queries) | |||
| Mixed Cell Formatiing | Setting up and Configuration of Excel | |||
| Mixed data types in a cell | Excel Discussion (Misc queries) | |||
| Mixed Cell | Excel Worksheet Functions | |||
| Copying a mixed reference formula | Excel Discussion (Misc queries) | |||
Linear Mode
