Microsoft Excel Question:
Download Questions PDF

Excel Uses for the INDIRECT and ADDRESS Functions or Formulas?


To increment a formula reference by rows, when copying across columns you need a combination of the INDIRECT and ADDRESS Function. For example, place this formula in any row in Column "D".


Now copy it across any amount of Columns. You will note that at each Column the row number increases by 1. In affect it will look like:

=SUM($A$1:$A$2) at Column D
=SUM($A$1:$A$3) at Column E
=SUM($A$1:$A$4) at Column F
=SUM($A$1:$A$5) at Column G

The Syntax for the ADDRESS Function is


The last 3 arguments (abs_num,a1,sheet_text) are all optional. If your range resides on another sheet, it is the sheet_text that you need, eg

=SUM(INDIRECT(ADDRESS(1,1,,,"Sheet 2")&":"&ADDRESS(COLUMN()-2,1)))

Note that the Sheet name is supplied to the first ADDRESS function only

Download MS Excel Interview Questions And Answers PDF

Previous QuestionNext Question
When I start XL, I get the error message Compile error in Hidden Module?How can I change the color of the sheet tabs?