Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 13th cell from another tab.
Is there a formula that will reference a cell in a seperate tab for every
13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 13th cell from another tab.
You can use the INDIRECT function. For example,
=INDIRECT("Equity!E"&166+(COLUMN()-18)*13) Here, change the "E" to the column letter from which to pull the values from the Equity sheet, change 166 to the first row on Equity from which data should be pulled, change the 18 to the column number of the first cell in which the formula appears (in this case, 18 = column R), and change the 13 to the number of rows to skip when pulling data off the Equity sheet. Then select the cells that are to contain the results and CTRL R to fill to the right. Rather than hard-coding these values in to the formula, you might want to put the values in cells and reference those cells in the formula. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "jordanpcpre" wrote in message ... Is there a formula that will reference a cell in a seperate tab for every 13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 13th cell from another tab.
Hi
One way would be to use 2 cells on your sheet to hold the starting row number (166) and the offset (13). lets say these are in cells R1 and R2 respectively. in cell R7 =INDEX(+Equity!$E:$E,$R$1+($R$2*(COLUMN(A1)-1))) Copy across as far as required -- Regards Roger Govier "jordanpcpre" wrote in message ... Is there a formula that will reference a cell in a seperate tab for every 13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 13th cell from another tab.
Here is the formula that I have inputed from the information you provided;
however I am still getting a #NUM! error. =INDEX(+Equity!$E:$E,$E127+($E$13*(COLUMN()-5))) I'm in the 'IRR' tab working in row 5, and need to reference cells in the 'Equity' tab. I need to reference Equity!E127, and then every 13th row (in column e) after that. I would like to be able to drag this formula across row 5 (in the IRR tab) and reference every 13th row (in the Equity tab) in column E. Thank you for the help! "Roger Govier" wrote: Hi One way would be to use 2 cells on your sheet to hold the starting row number (166) and the offset (13). lets say these are in cells R1 and R2 respectively. in cell R7 =INDEX(+Equity!$E:$E,$R$1+($R$2*(COLUMN(A1)-1))) Copy across as far as required -- Regards Roger Govier "jordanpcpre" wrote in message ... Is there a formula that will reference a cell in a seperate tab for every 13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 13th cell from another tab.
Hi
You don't need the column references. We are only dealing with numbers. If you are going to hard code the numbers into the formula, rather than holding them in other cells, then use =INDEX(+Equity!$E:$E,127+(13*(COLUMN()-5))) -- Regards Roger Govier "jordanpcpre" wrote in message ... Here is the formula that I have inputed from the information you provided; however I am still getting a #NUM! error. =INDEX(+Equity!$E:$E,$E127+($E$13*(COLUMN()-5))) I'm in the 'IRR' tab working in row 5, and need to reference cells in the 'Equity' tab. I need to reference Equity!E127, and then every 13th row (in column e) after that. I would like to be able to drag this formula across row 5 (in the IRR tab) and reference every 13th row (in the Equity tab) in column E. Thank you for the help! "Roger Govier" wrote: Hi One way would be to use 2 cells on your sheet to hold the starting row number (166) and the offset (13). lets say these are in cells R1 and R2 respectively. in cell R7 =INDEX(+Equity!$E:$E,$R$1+($R$2*(COLUMN(A1)-1))) Copy across as far as required -- Regards Roger Govier "jordanpcpre" wrote in message ... Is there a formula that will reference a cell in a seperate tab for every 13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 13th cell from another tab.
Thank you for the help Roger. We almost have it.
My current formula in cell IRR!L7: =INDEX(Equity!$E:$E,127+(COLUMN( )-5)*13) I am in cell L7 of the IRR tab trying to reference cell E127 in the Equity tab. Should the above formula reference the Equity!E127 cell correctly? I would then like to drag this formula one column to the right and have it reference automatically 13 rows below Equity!E127 (so IRR!M7 should reference Equity!E140). I would like to drag this formula to the right as far as necessary. Thanks! We almost have it! "Roger Govier" wrote: Hi You don't need the column references. We are only dealing with numbers. If you are going to hard code the numbers into the formula, rather than holding them in other cells, then use =INDEX(+Equity!$E:$E,127+(13*(COLUMN()-5))) -- Regards Roger Govier "jordanpcpre" wrote in message ... Here is the formula that I have inputed from the information you provided; however I am still getting a #NUM! error. =INDEX(+Equity!$E:$E,$E127+($E$13*(COLUMN()-5))) I'm in the 'IRR' tab working in row 5, and need to reference cells in the 'Equity' tab. I need to reference Equity!E127, and then every 13th row (in column e) after that. I would like to be able to drag this formula across row 5 (in the IRR tab) and reference every 13th row (in the Equity tab) in column E. Thank you for the help! "Roger Govier" wrote: Hi One way would be to use 2 cells on your sheet to hold the starting row number (166) and the offset (13). lets say these are in cells R1 and R2 respectively. in cell R7 =INDEX(+Equity!$E:$E,$R$1+($R$2*(COLUMN(A1)-1))) Copy across as far as required -- Regards Roger Govier "jordanpcpre" wrote in message ... Is there a formula that will reference a cell in a seperate tab for every 13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 13th cell from another tab.
Hi
Then go back to almost how I had sent the first formula to you, but substituting your fixed numeric values in place of the cell references. =INDEX(Equity!$E:$E,127+(COLUMN(A1)-1)*13) Column(A1) will return 1, whatever cell the formula is entered into, so COLUMN(A1)-1 will be 0, and multiplying 13*0 = 0 so there will be no addition to the starting row number (127) As you copy across the page, COLUMN(A1) will become COLUMN(B1) etc, so the add on to 127 will become 1*13, 2*13 etc. -- Regards Roger Govier "jordanpcpre" wrote in message ... Thank you for the help Roger. We almost have it. My current formula in cell IRR!L7: =INDEX(Equity!$E:$E,127+(COLUMN( )-5)*13) I am in cell L7 of the IRR tab trying to reference cell E127 in the Equity tab. Should the above formula reference the Equity!E127 cell correctly? I would then like to drag this formula one column to the right and have it reference automatically 13 rows below Equity!E127 (so IRR!M7 should reference Equity!E140). I would like to drag this formula to the right as far as necessary. Thanks! We almost have it! "Roger Govier" wrote: Hi You don't need the column references. We are only dealing with numbers. If you are going to hard code the numbers into the formula, rather than holding them in other cells, then use =INDEX(+Equity!$E:$E,127+(13*(COLUMN()-5))) -- Regards Roger Govier "jordanpcpre" wrote in message ... Here is the formula that I have inputed from the information you provided; however I am still getting a #NUM! error. =INDEX(+Equity!$E:$E,$E127+($E$13*(COLUMN()-5))) I'm in the 'IRR' tab working in row 5, and need to reference cells in the 'Equity' tab. I need to reference Equity!E127, and then every 13th row (in column e) after that. I would like to be able to drag this formula across row 5 (in the IRR tab) and reference every 13th row (in the Equity tab) in column E. Thank you for the help! "Roger Govier" wrote: Hi One way would be to use 2 cells on your sheet to hold the starting row number (166) and the offset (13). lets say these are in cells R1 and R2 respectively. in cell R7 =INDEX(+Equity!$E:$E,$R$1+($R$2*(COLUMN(A1)-1))) Copy across as far as required -- Regards Roger Govier "jordanpcpre" wrote in message ... Is there a formula that will reference a cell in a seperate tab for every 13th cell. For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be 13 rows below =+Equity!E166... So, R7 equals =+Equity!E166 S7 equals =+Equity!E179 T7 equals =+Equity!E192 I need to repeat this for multiple colums. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I Calculate Check Digit for UPC A - the 13th warrior | Excel Discussion (Misc queries) | |||
copy only the 1st, 7th, 13th, 19th, 25th.....row only | Excel Discussion (Misc queries) | |||
Sum function that add every 13th row... | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |