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.
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.
ExpiresDefault access plus 1 year
interesting concept! :) your blog is really useful. Please continue to update. Btw, I also found that the Index & Match formula could also work to produce the same result, and only 2 formulas will be required instead of 3 :)
ReplyDelete