Formulas

I

Ian Burns

I need to copy a formula from one cell to another but I
need it to increase by 7 columns when I copy.Eg =sum
(a1:g1) when copied form cell to another will result in
=sum(b1:h1), but I want it to copy =sum(h1:n1)rather than
having to copy it and then adjust manually. There must be
a way of of copying with constant gaps from one cell to
another. It sounds simple but I cannot find away even by
devising a custom function. Please help
 
R

Ryan

The simplest way to get around this is to insert 6 more
rows after the one you want to copy. Now copy and paste
all seven rows and the formula will jump the 7 rows as
needed. I do this often to crunch large numbers of data
points. After you are done copying it is easy to delete
the rows and the formula will retain intact.
Ryan Goodwin
 
J

Jason Morin

You could use this anywhere in column 1 and drag across:

=SUM(OFFSET($A$1,,COLUMN()*7-7,,7))

HTH
Jason
Atlanta, GA
 
Top