formula fill

F

frank

I'm trying to fill formulas down skipping cells from another sheet,
='Sheet 1'!J682
='Sheet 1'!J689
='Sheet 1'!J705
='Sheet 1'!J712
='Sheet 1'!J719
and so on, skipping 6 cells in between and want to know the easiest way if there is a way? I'm using Excel 2002
thanks
 
F

Frank Kabel

Hi
enter the following formula:
=OFFSET('Sheet 1'!$J$682,(ROW(1:1)-1)*7,0)

Note: I think in your example the 3rd formula is wrong. Should be:
='Sheet 1'!J706
and all following formulas are also off by one row
 
F

frank

Thanks Frank,
Actually even the first formula was off, should be
='Sheet 1'!D684
='Sheet 1'!D691
='Sheet 1'!D698
='Sheet 1'!D705
='Sheet 1'!D712
='Sheet 1'!D719
Anyway...
In the above would I replace the
='Sheet 1'!D684
with your formula
=OFFSET('Sheet 1'!$J$684,(ROW(1:1)-1)*7,0)
And then it should fill down the column correctly?
I have been filling in the formulas manually and want to get away from this, would this have to have been started from the first entry of the formula?
I have tried to enter your formula in place of mine with the correct cell referenced and nada... blank cell
 
F

Frank Kabel

Hi
if you enter
=OFFSET('Sheet 1'!$J$684,(ROW(1:1)-1)*7,0)
in your first cell and copy this formula down it should work. What
happens for you?
 
F

frank

hi,
for one, I'm trying to start my formula fill in cell D9 of sheet named "Print", was actually trying to start it in cell D41 of the same sheet but figured it should be started on the first cell which still isn't in the first row... and cell D9 needs to = cell D41 of Sheet 1, the sheet is actually named “Imported Dataâ€, which is updated automatically when I open it, (from a .csv file), this file has more information then I need printed and this is the reason I want to eliminate the rows in between. It seems like in the formual you sent , ROW refers to the same page as the formula is in and should refer back to the Imported Data or Sheet 1. I could send you the file if you would like, I appreciate your time and patients. I must seem a bit thick and I apologize, thanks
Frank
 
F

frank

think I got it Frank,
does this look correct? =OFFSET('Imported Data'!D481,(ROW(7:7)-7)*6,0) ?
 
Top