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

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.