Apr 18 2020 07:29 AM
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!!!!!!!!!
Apr 18 2020 08:38 AM
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)
Apr 18 2020 09:57 AM
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
Apr 18 2020 11:33 AM
It shall be offset calculated on columns, like
=OFFSET(AW!$D$499,(COLUMN()-COLUMN($A$1))*38,0)