Refer cell value as Worksheet Name in Excel formula

Use Cell Value as Worksheet Name in Excel Formula

Sometimes we have different worksheets in same workbook and we need some data for calculation from these sheet in any sheet. We can get data from worksheet using reference of cell of specific worksheet.  But we want to use cell value in which sheet name has been mentioned and whenever we Change the value of cell, data or calculation will change automatically based on value of cell referred in formula. We have Regional Sales data worksheets for each month in same workbook. refer to below pic.

Using the below formula we can easily pull sum of Total of Jan Month using sumif function bas per below formula.

We want to make us this formula dynamic. If we change month in C4 cell, data for sales pull from relevant month’s worksheet automatically.

We can achieve this using Indirect function with Sumif. In the Indirect function a cell reference has to mention then indirect function move the pointer to the cell value. E.g. we can mention month in C4 and refer C4 in the indirect function as per below formula.

In the above formula we have used Sumif function of Excel with Indirect function. In the Indirect function we referred C4 Cell having month name which contain sheet name available in this workbook and combined the range using double quotation mark and made the range as absolute reference so that range of each sheet become constant.

Please make sure the Data to be retrieve would be in same column in all worksheets otherwise you will not get desired result.