Forum Discussion

Iron Contributor

Mar 01, 2022

Increase Cell References by X in Formulas Macro

Hello, I have been searching the net for a possible solution but I couldn't find what I need wherever I looked. I need to increment all cell references in formulas by X or 1 in a selected o...

Formulas and Functions

OliverScheurich

Gold Contributor

Mar 01, 2022

=INDIRECT("A"&ROW($A$3)+(ROW(A1)-1)*$F$1)

Maybe like this? In cell F1 you can enter the X or 1 by which you want the cell reference to increment.

cell reference increment.pdf207 KB

Iron Contributor

Mar 01, 2022

Hmm would that work with different formulas? Because many of the cells have different formulas.

OliverScheurich
Gold Contributor
Mar 01, 2022
kheldar
Can you share the other formulas? In principle, this should also be possible with other formulas.
- kheldar
  Iron Contributor
  Mar 01, 2022
  OliverScheurich
  
  Well they are fairly simple since they are to build a lookup table from a horribly designed table.
  
  =IF('Queue Assignment - Mid'!A27=0,"",'Queue Assignment - Mid'!A27) =IF(OR('Queue Assignment - Mid'!H6=0,'Queue Assignment - Mid'!H6="No Lunch"),"",'Queue Assignment - Mid'!H6) =LET(calc,'Queue Assignment - Morning'!E6,IF(calc=0,"",calc))
  
  I switched to LET after awhile because I got lazy with copy pasting. should have done it earlier .
  
  But I should let you know that I'm resorting to a macro because I can't just click and drag formulas because almost all of them are different in a way that it doesn't allow flashfill.
  - OliverScheurich
    Gold Contributor
    Mar 01, 2022
    kheldar
    Sub Makro1() ActiveCell.FormulaR1C1 = _ "=IF('Queue Assignment - Mid'!R[20]C[-1]=0,"""",'Queue Assignment - Mid'!R[20]C[-1])" End Sub
    These lines were recorded with the macro recorder. You can activate the cell where you want to enter the formula and run the macro. This could be what you want to do.

Resources