If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
cell references
does anyone know how to enter a formula to link two pages
when the cell reference always increases by 7 rows. Right now I have to type the formula in each cell, I am trying to figure out how I can copy the formula across. For example Cell A1 ='tommy'!K10 Cell B1 ='tommy'!K17 Cell C1 ='tommy'!K24 etc. Any suggestions? |
#2
|
|||
|
|||
cell references
One way
=OFFSET(Tommy!$K$10,ROW(1:1)*7-7,) don't insert rows above the formula or it will be offset wrongly -- Regards, Peo Sjoblom "Sandi" wrote in message ... does anyone know how to enter a formula to link two pages when the cell reference always increases by 7 rows. Right now I have to type the formula in each cell, I am trying to figure out how I can copy the formula across. For example Cell A1 ='tommy'!K10 Cell B1 ='tommy'!K17 Cell C1 ='tommy'!K24 etc. Any suggestions? |
#3
|
|||
|
|||
cell references
This may be odd but make a column adding 7 to the item
above. Do a find and replace all = with = creating text. concatenate formula minus cell refrence row # and the column with correct numbers.the name with the column with correct numbers =Concatenate(='tommy'!K,a2) Copy paste special values then find replace = with = to reactivate formula. -----Original Message----- does anyone know how to enter a formula to link two pages when the cell reference always increases by 7 rows. Right now I have to type the formula in each cell, I am trying to figure out how I can copy the formula across. For example Cell A1 ='tommy'!K10 Cell B1 ='tommy'!K17 Cell C1 ='tommy'!K24 etc. Any suggestions? . |
#4
|
|||
|
|||
cell references
Sandi,
If I'm not mistaken, if you type 3 or 4 in you can highlight all of them (the 3 or 4 that you typed)and click the solid black box in the bottom right corner of the cells and drag it down. Once you begin to create a series, it remembers that and should continue it for you. -----Original Message----- does anyone know how to enter a formula to link two pages when the cell reference always increases by 7 rows. Right now I have to type the formula in each cell, I am trying to figure out how I can copy the formula across. For example Cell A1 ='tommy'!K10 Cell B1 ='tommy'!K17 Cell C1 ='tommy'!K24 etc. Any suggestions? . |
#5
|
|||
|
|||
cell references
"Peo Sjoblom" wrote in message ... One way =OFFSET(Tommy!$K$10,ROW(1:1)*7-7,) don't insert rows above the formula or it will be offset wrongly -- Regards, Peo Sjoblom Very neat! I wish I'd thought of that. BTW the OP mentioned A1, B1, C1, etc, rather than A1, A2, A3. A formula that can be copied across rather than down is: =OFFSET($K$10,COLUMN(A1)*7-7,) Geoff "Sandi" wrote in message ... does anyone know how to enter a formula to link two pages when the cell reference always increases by 7 rows. Right now I have to type the formula in each cell, I am trying to figure out how I can copy the formula across. For example Cell A1 ='tommy'!K10 Cell B1 ='tommy'!K17 Cell C1 ='tommy'!K24 etc. Any suggestions? |
Thread Tools | |
Display Modes | |
|
|