| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to increment a cell reference in a row
I have a row of cells. The first cell in the row is ='05.09'!M7. I need the
second cell in the same row to be incremented by 7 ex: ='05.09'!M14 and the following cells in the same row to be incremented by 7 continously. It would look like this cell one ='05.09'!M7 cell two ='05.09'!M14 cell three ='05.09'!M21 cell four ='05.09'!M28. How can I add 7 to the first cell and drag over the remaining cells in the row. The data from ='05.09'!M7 is in a different worksheet and in columns not rows. Any help would be appreciated. Thanks, -- Mike |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to increment a cell reference in a row
In row 1 enter the below formula and copy that down..It picks the row number
and multiply by 7 =INDIRECT("05.09!M" & ROW()*7) If this post helps click Yes --------------- Jacob Skaria "Mike" wrote: I have a row of cells. The first cell in the row is ='05.09'!M7. I need the second cell in the same row to be incremented by 7 ex: ='05.09'!M14 and the following cells in the same row to be incremented by 7 continously. It would look like this cell one ='05.09'!M7 cell two ='05.09'!M14 cell three ='05.09'!M21 cell four ='05.09'!M28. How can I add 7 to the first cell and drag over the remaining cells in the row. The data from ='05.09'!M7 is in a different worksheet and in columns not rows. Any help would be appreciated. Thanks, -- Mike |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to increment a cell reference in a row
Hi,
Try this =INDEX('05.09'!M:M,(ROW(A1)-1)*7+7) Mike "Mike" wrote: I have a row of cells. The first cell in the row is ='05.09'!M7. I need the second cell in the same row to be incremented by 7 ex: ='05.09'!M14 and the following cells in the same row to be incremented by 7 continously. It would look like this cell one ='05.09'!M7 cell two ='05.09'!M14 cell three ='05.09'!M21 cell four ='05.09'!M28. How can I add 7 to the first cell and drag over the remaining cells in the row. The data from ='05.09'!M7 is in a different worksheet and in columns not rows. Any help would be appreciated. Thanks, -- Mike |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to increment a cell reference in a row
Jacob,
Suitably modified it doesn't have to go in row 1 =INDIRECT("05.09!M" & (ROW(A1)-1)*7+7) Mike "Jacob Skaria" wrote: In row 1 enter the below formula and copy that down..It picks the row number and multiply by 7 =INDIRECT("05.09!M" & ROW()*7) If this post helps click Yes --------------- Jacob Skaria "Mike" wrote: I have a row of cells. The first cell in the row is ='05.09'!M7. I need the second cell in the same row to be incremented by 7 ex: ='05.09'!M14 and the following cells in the same row to be incremented by 7 continously. It would look like this cell one ='05.09'!M7 cell two ='05.09'!M14 cell three ='05.09'!M21 cell four ='05.09'!M28. How can I add 7 to the first cell and drag over the remaining cells in the row. The data from ='05.09'!M7 is in a different worksheet and in columns not rows. Any help would be appreciated. Thanks, -- Mike |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to increment a cell reference in a row
Sorry, I might have not explained myself very well so here is an example of
what I am trying to do. Cell 1 needs to pull from 05.09'!M7 to get the value 22. Then I would like to put into Cell 2, Cell 3 and Cell 4 a formula to increment 05.09'!M7 by 7 so I can avoid so much keying. Consequently, I am pulling from columns and placing the data into rows. I tried the =INDIRECT("05.09!M" & ROW()*7) but it did not work. Also tried Column inplace if row. Worksheet Reptg Data in Rows Cell 1 Cell 2 Cell 3 Cell 4 =05.09'!M7 22 =05.09'!M14 33 =05.09'!M21 44 =05.09'!M28 55 Worksheet 05.09 in columns 05.09'!M7 value is 22 05.09'!M14 value is 33 05.09'!M21 value is 44 05.09'!M28 value is 55 This help? -- Mike "Jacob Skaria" wrote: In row 1 enter the below formula and copy that down..It picks the row number and multiply by 7 =INDIRECT("05.09!M" & ROW()*7) If this post helps click Yes --------------- Jacob Skaria "Mike" wrote: I have a row of cells. The first cell in the row is ='05.09'!M7. I need the second cell in the same row to be incremented by 7 ex: ='05.09'!M14 and the following cells in the same row to be incremented by 7 continously. It would look like this cell one ='05.09'!M7 cell two ='05.09'!M14 cell three ='05.09'!M21 cell four ='05.09'!M28. How can I add 7 to the first cell and drag over the remaining cells in the row. The data from ='05.09'!M7 is in a different worksheet and in columns not rows. Any help would be appreciated. Thanks, -- Mike |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to increment a cell reference in a row
Jacob's formula does exactly that but you must put it in row 1. My formula
can be put in any row and when dragged down gives the reult you want Mike "Mike" wrote: Sorry, I might have not explained myself very well so here is an example of what I am trying to do. Cell 1 needs to pull from 05.09'!M7 to get the value 22. Then I would like to put into Cell 2, Cell 3 and Cell 4 a formula to increment 05.09'!M7 by 7 so I can avoid so much keying. Consequently, I am pulling from columns and placing the data into rows. I tried the =INDIRECT("05.09!M" & ROW()*7) but it did not work. Also tried Column inplace if row. Worksheet Reptg Data in Rows Cell 1 Cell 2 Cell 3 Cell 4 =05.09'!M7 22 =05.09'!M14 33 =05.09'!M21 44 =05.09'!M28 55 Worksheet 05.09 in columns 05.09'!M7 value is 22 05.09'!M14 value is 33 05.09'!M21 value is 44 05.09'!M28 value is 55 This help? -- Mike "Jacob Skaria" wrote: In row 1 enter the below formula and copy that down..It picks the row number and multiply by 7 =INDIRECT("05.09!M" & ROW()*7) If this post helps click Yes --------------- Jacob Skaria "Mike" wrote: I have a row of cells. The first cell in the row is ='05.09'!M7. I need the second cell in the same row to be incremented by 7 ex: ='05.09'!M14 and the following cells in the same row to be incremented by 7 continously. It would look like this cell one ='05.09'!M7 cell two ='05.09'!M14 cell three ='05.09'!M21 cell four ='05.09'!M28. How can I add 7 to the first cell and drag over the remaining cells in the row. The data from ='05.09'!M7 is in a different worksheet and in columns not rows. Any help would be appreciated. Thanks, -- Mike |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to increment a cell reference in a row
Thanks,
The =INDIRECT("05.09!M" & (ROW(A1)-1)*7+7) worked great. I did need to change the (ROW to (COLUMN. After that it worked great. Much thanks, this will save me time and increase the accuracy of the report. -- Mike "Mike H" wrote: Jacob, Suitably modified it doesn't have to go in row 1 =INDIRECT("05.09!M" & (ROW(A1)-1)*7+7) Mike "Jacob Skaria" wrote: In row 1 enter the below formula and copy that down..It picks the row number and multiply by 7 =INDIRECT("05.09!M" & ROW()*7) If this post helps click Yes --------------- Jacob Skaria "Mike" wrote: I have a row of cells. The first cell in the row is ='05.09'!M7. I need the second cell in the same row to be incremented by 7 ex: ='05.09'!M14 and the following cells in the same row to be incremented by 7 continously. It would look like this cell one ='05.09'!M7 cell two ='05.09'!M14 cell three ='05.09'!M21 cell four ='05.09'!M28. How can I add 7 to the first cell and drag over the remaining cells in the row. The data from ='05.09'!M7 is in a different worksheet and in columns not rows. Any help would be appreciated. Thanks, -- Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro - How to increment cell reference by one row | Excel Worksheet Functions | |||
| increment cell reference in a formula | Excel Worksheet Functions | |||
| Need formula to increment cell reference every 8th row | Excel Discussion (Misc queries) | |||
| Increment cell reference | Excel Discussion (Misc queries) | |||
| how to make cell address reference increment? | Excel Worksheet Functions | |||
Linear Mode
