Forum Discussion

RVBWa's avatar
RVBWa
Copper Contributor
Apr 18, 2020

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!!!!!!!!!

Formulas and Functions

3 Replies

  • Charla74's avatar
    Charla74
    Iron Contributor
    Apr 18, 2020

    RVBWa 

     

    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)

    • RVBWa's avatar
      RVBWa
      Copper Contributor
      Apr 18, 2020

      Charla74Hi

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 18, 2020

        RVBWa 

        It shall be offset calculated on columns, like

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

         

Resources