Relative Referencing - Steps
I am trying to do relative referencing for a specific column (in Sheet "OA") where I need to retrieve the content of cells in a new sheet in steps of 110
=OA!$AB217 =OA!$AB327 =OA!$AB437
Rather than manually code the formula in each cell, trying to drag the cells so the address could be dynamically generated.
On the above example, when I drag the cell, I am unable to get
I also tried to code it this way, "=OA!$AB($107+(n*$110))" where when the n values naturally incremented during the drag, I will get the right formula.
=OA!$AB($107+(1*$110)) => =OA!$AB217 =OA!$AB($107+(2*$110)) => =OA!$AB327 =OA!$AB($107+(3*$110)) => =OA!$AB437 =OA!$AB($107+(4*$110)) => =OA!$AB547
Unfortunately the format is not accepted by excel.
Could you please give me a hint what mistake I could have made ? My search on the internet have not been fruitful.
rgds Saravanan K
You're looking for the "Indirect" function. Assemble a text string with the address of the cell you want, and call =indirect("string"). You can use the "&" operator to combine static strings with variables.
I've created a workbook with Sheet1 and OA OA!A1:A1000 contain the numbers.
Sheet1!C1 contains 1 Sheet1!C2 contains 100
Sheet1!B1 contains =INDIRECT("OA!$A"&C1) Sheet1!B2 contains =INDIRECT("OA!$A"&C2)