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



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


but getting


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)

Need Your Help

Facebook graph api comment count

facebook api facebook-graph-api count facebook-comments

seems Facebook changed the result of posts, few weeks ago it was possible to read the comment count from the post directly

How to not send a response body

php restler

I'm trying to send a header response back from our api with a http status code 201 Created and a Location:header.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.