How to use "INDIRECT" function

Lets say we have production log sheet for technicians to put a "x" in the relevant cell, as shown below. Technicians assigned working on the particular date enters a cross "x" in the corresponding row and corresponding column (his/her name).

Then we need to get all the names in column C. This will be very help full in many ways. 

Use formula in cell C2 and populate as needed. 

=INDIRECT(ADDRESS(2,MATCH("x",D3:K3,0)+COLUMN()))

Lets analyse the formula, 

1. INDIRECT needs the cell reference.

2. To get the reference cell I am using ADDRESS function. Address function requires row number and column number.

3. Row number is 2, as that is the contains name of the technicians.

4. Column number is bit complicated. Use MATCH function to find the column number within the selected range (in this example D3:K3). Use COLUMN function to find the column number where the result to be displayed (column C in this example).  

MATCH("x",D3:K3,0)+COLUMN()

The result will be like image below,

Comments are welcome.

How to use COLUMN and COLUMNS functions.

Lets look at the two functions which sound similar but give different results. COLUMN gives the position of the column and COLUMNS counts the number of columns in a range.

Let's see the syntax.

COLUMN([reference])

COLUMNS(array)

Examples
COLUMN(F2) = 6 
COLUMN() = will give the column position of the cell where the function is.

COLUMNS(F10:I14) = 4






One use of the COLUMN function is with VLOOKUP. 
Consider the example below, A table of outputs of different machines in different days.

Now I want to pull only machine 3 data to another place using VLOOKUP. First I key in Machine 3 in A6 for this example.
I need the values in column I to L to appear in column B to E.

I can key in =VLOOKUP($A6,$H:$L,2,FALSE) in B6, =VLOOKUP($A6,$H:$L,3,FALSE) in C5 and so on. 
But I need to keep changing the col_index_num  for every column. If you are working with a large table it can be tedious work.

Here we can use column function to make it automatic when we drag the function. The formula looks like this,

in B6 =VLOOKUP($A6,$H:$L,COLUMN(I:I)-7,FALSE) 

COLUMN(I:I)-7 = 2

When you drag the function,
in C6 =VLOOKUP($A6,$H:$L,COLUMN(J:J)-7,FALSE)
in D6 =VLOOKUP($A6,$H:$L,COLUMN(K:K)-7,FALSE)
in E6 =VLOOKUP($A6,$H:$L,COLUMN(L:L)-7,FALSE)

You can use COLUMNS(A:G) instead of 7
=VLOOKUP($A6,$H:$L,COLUMN(I:I)-COLUMNS(A:G),FALSE)

Depending on the table location and where you want to pull the data the function for col_index_num will change. 


Please let me know comments/feedback.

Using ADDRESS function

Lets look at the syntax first.

Syntax

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

1. row_num can be specified by numerical or by row() function. 

2. column_num can be specified by numerical or  by column() function.

3. abs_num has 4 choices,

abs_num	Returns this type of reference
1 or omitted	Absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.)
2	Absolute row; relative column
3	Relative row; absolute column
4	Relative

4. a1 has two options, TRUE, 1 or omitted and FALSE or 0. TRUE, 1 or omitted will give A1 style and FALSE will give R1C1 style. 

5. sheet_text specifies the sheet name in which the cell is located. Please note sheet names are case sensitive.

Lets look at the different possibilities.

Formula	Results
=ADDRESS(1,1)	$A$1
=ADDRESS(1,1,1)	$A$1
=ADDRESS(1,1,1,1)	$A$1
=ADDRESS(1,1,1,0)	R1C1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(ROW(A1),COLUMN(A1),1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,2,1,"Sheet1")	Sheet1!A$1
=ADDRESS(1,1,3,1,"Sheet1")	Sheet1!$A1
=ADDRESS(1,1,4,1,"Sheet1")	Sheet1!A1

Simply put this function will give the cell number. So whats the big deal about a cell number. Well it can be used in labeling.

Example

Look at the example below,

It works fine till a row or column added or removed. 

Now the label become meaningless as name should be entered in E3. 

The solution is to use ADDRESS function.

="Please enter the name in cell "&ADDRESS(ROW(E2),COLUMN(E2),4,)

It works fine even a row or column added or removed. By this way the label auto adjusts.

Add a row...

Add a column...

The label adjusts automatically.

In the same way a range can be specified in a label. Using formula as shown below,

="Please enter the name in cell "&ADDRESS(ROW(F3),COLUMN(F3),4,)&":"&ADDRESS(ROW(F20),COLUMN(F20),4,) 

Love to have your comments.