get a value of a cell in a sheet

[taherelchoufi]

dear sirs,
please i have a workbook, in this workbook there's different sheets, i add a sheet with name summary, in this sheet i create 3 columns, the first is the index of sheets, the second one i create a formula to get name of sheet in the index exist in the first column, the column i want to get a value of a cell from the sheet in the second column.
for example
cell C1 WILL BE THE VALUE OF CELL F7 IN THE SHEET WITH NAME EXIST IN THE CELL B1.
best regards,

=

 

[Yousuf Shovon]

please i have a workbook, in this workbook there's different sheets, i add a sheet with name summary, in this sheet i create 3 columns, the first is the index of sheets, the second one i create a formula to get name of sheet in the index exist in the first column, the column i want to get a value of a cell from the sheet in the second column.

Hello Taherelchoufi,
Welcome to our ExcelDemy forum! Thanks for sharing your experience with us!
I understand you want to extract a value from a sheet based on the sheet name and index number. You can use the INDIRECT function or the combination of INDEX and MATCH with the INDIRECT function to achieve this.
Here are the steps to get the value of a cell from a specific sheet based on their given sheet name and index number:
1. Use the below formula and press Enter:

=INDIRECT("'"&B2&"'!F7")

2. Drag the formula down.
The formula uses the value in cell B2 as the sheet name and constructs a reference to cell F7 in that sheet using the INDIRECT function, allowing for dynamic referencing of a specific cell across sheets in Excel. The result is the value in cell F7 of the sheet specified by the content of cell B2.

To make the formula more robust, you can use the INDEX function with MATCH to ensure accurate sheet name retrieval, even if the list of sheets is in a different order. Here is the formula:

=INDIRECT("'"&INDEX(B:B,MATCH(A2,A:A,0))&"'!F7")

The formula retrieves the value in cell F7 from a sheet whose name is specified in cell B corresponding to the index found in cell A2, using the INDIRECT, INDEX, and MATCH functions in Excel.

Hope this works for you. Let us know which formula you like better. I am attaching the workbook for your practice.

Regards,
Yousuf Shovon

 

[taherelchoufi]

dear Mr., Shovon,
thank you very much, this help me very will,
best regards,