Highlighted
New Contributor

# Increase the cell reference in a formula by 38 rows

Hi.  I have a worksheet with many formulas in a column which are linked to another worksheet via  formulas i.e =AW!\$D\$499, AW!D\$500, AW!\$D\$521 etc...).  I have been manually adding 38 (the number of rows between each section I want) to everyone of these formulas in my new colomn so it links to a different section of that worksheet i.e =AW!\$D\$499 in column A becomes AW!\$D\$537 in column B.

Is there anyway I can do this via another formula instead of adding 38 to the last number each time?  I have hundreds to do!

Many thanks!!!!!!!!!

3 Replies
Highlighted

# Re: Increase the cell reference in a formula by 38 rows

i.e =AW!\$D\$499 in column A becomes AW!\$D\$537 in column B.

Is there anyway I can do this via another formula instead of adding 38 to the last number each time?

Try this in column A then drag across to column B...

=OFFSET(AW!\$D\$499,(ROW()-1)*38,0)

Highlighted

# Re: Increase the cell reference in a formula by 38 rows

Thank you for your reply.  Unfortunately it didn't work. Nothing happened to the formula when I dragged it to the right (I even tried removing the \$ on the cell number reference).

The cell reference (on AW) is a 'Yes' or 'No' and copies that into my sheet.  Each column is 38 rows down on the previous formula.

Thank you again for trying my friend

Highlighted

# Re: Increase the cell reference in a formula by 38 rows

It shall be offset calculated on columns, like

``=OFFSET(AW!\$D\$499,(COLUMN()-COLUMN(\$A\$1))*38,0)``