Increase the cell reference in a formula by 38 rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1318102%22%20slang%3D%22en-US%22%3EIncrease%20the%20cell%20reference%20in%20a%20formula%20by%2038%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318102%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%26nbsp%3B%20I%20have%20a%20worksheet%20with%20many%20formulas%20in%20a%20column%20which%20are%20linked%20to%20another%20worksheet%20via%26nbsp%3B%20formulas%20i.e%20%3CFONT%3E%3DAW!%24D%24499%2C%20AW!D%24500%2C%20%3CFONT%3EAW!%24D%3C%2FFONT%3E%24521%20etc...)%3C%2FFONT%3E.%26nbsp%3B%20I%20have%20been%20manually%20adding%2038%20(the%20number%20of%20rows%20between%20each%20section%20I%20want)%20to%20everyone%20of%20these%20formulas%20in%20my%20new%20colomn%20so%20it%20links%20to%20a%20different%20section%20of%20that%20worksheet%20i.e%26nbsp%3B%3CFONT%3E%3D%3CFONT%3EAW!%24D%24499%20in%20column%20A%20becomes%20%3C%2FFONT%3EAW!%24D%24537%20in%20column%20B.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIs%20there%20anyway%20I%20can%20do%20this%20via%20another%20formula%20instead%20of%20adding%2038%20to%20the%20last%20number%20each%20time%3F%26nbsp%3B%20I%20have%20hundreds%20to%20do!%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EMany%20thanks!!!!!!!!!%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1318102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318235%22%20slang%3D%22en-US%22%3ERe%3A%20Increase%20the%20cell%20reference%20in%20a%20formula%20by%2038%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627412%22%20target%3D%22_blank%22%3E%40RVBWa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e%26nbsp%3B%3CFONT%3E%3DAW!%24D%24499%20in%20column%20A%20becomes%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAW!%24D%24537%20in%20column%20B.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIs%20there%20anyway%20I%20can%20do%20this%20via%20another%20formula%20instead%20of%20adding%2038%20to%20the%20last%20number%20each%20time%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3ETry%20this%20in%20column%20A%20then%20drag%20across%20to%20column%20B...%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3DOFFSET(AW!%24D%24499%2C(ROW()-1)*38%2C0)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318333%22%20slang%3D%22en-US%22%3ERe%3A%20Increase%20the%20cell%20reference%20in%20a%20formula%20by%2038%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3EHi%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%26nbsp%3B%20Unfortunately%20it%20didn't%20work.%20Nothing%20happened%20to%20the%20formula%20when%20I%20dragged%20it%20to%20the%20right%20(I%20even%20tried%20removing%20the%20%24%20on%20the%20cell%20number%20reference).%3C%2FP%3E%3CP%3EThe%20cell%20reference%20(on%20AW)%20is%20a%20'Yes'%20or%20'No'%20and%20copies%20that%20into%20my%20sheet.%26nbsp%3B%20Each%20column%20is%2038%20rows%20down%20on%20the%20previous%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20trying%20my%20friend%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318472%22%20slang%3D%22en-US%22%3ERe%3A%20Increase%20the%20cell%20reference%20in%20a%20formula%20by%2038%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318472%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627412%22%20target%3D%22_blank%22%3E%40RVBWa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20shall%20be%20offset%20calculated%20on%20columns%2C%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DOFFSET(AW!%24D%24499%2C(COLUMN()-COLUMN(%24A%241))*38%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

@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)

Highlighted

@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

Highlighted

@RVBWa 

It shall be offset calculated on columns, like

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