While preparing a report, if we want to copy the contents of a specific cell from all the sheets in a specific row then we can use a combination of INDIRECT & COLUMN functions.
INDIRECT: Returns the reference specified by a text string.
ref_text is a reference to a cell. If ref_text is not a valid cell reference then INDIRECT function will return #REF error.
The second argument A1 refers to a logical value that specifies type of reference is contained in the cell ref_text.
If a1 is TRUE or omitted then ref_text is determined as A1-style reference.
If a1 is FALSE then ref_text is determined as R1C1 style reference.
Example: If value in cell A1 contains 10, B1 contains A1 & we use INDIRECT function in cell C1=INDIRECT(B1), then result would be 10
COLUMN: Returns the column number of a reference.
Reference: It is a reference to a cell or range of cells.
Let us take an example:
In this way we will find a formula very helpful that will give a value from all the multiple sheets in the workbook.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.