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

e.g,

=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

=OA!$AB547

but getting

=OA!$AB220

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.

E.g

=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

Answers


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)


Need Your Help

Using Custom Zend Validator in INI file

php zend-framework ini customvalidator

I don't have a custom Zend_Form and I just declared all the elements in the ini file. There is no problem with creating the Zend_Form from the ini file, but I am having problem using my own custom