Using sheet names in Excel
A specific problem … Suppose you have an Excel file with several worksheets – for example with sales figures per month – and you now want to get an overview of these figures in a table on a new worksheet. With two simple formulas you can achieve this in no time.
Formula 1: the row number
Let's start by creating a table on a new worksheet. In the first column we use the following formula to obtain the worksheet names. Note that our worksheets consist of the month and the year – e.g. "september 2021".
1=DATE(2021;ROW()-1;1)
Since we want to provide our table with headers, it is therefore important that we give the formula that -1. If we then set the format to mmmm yyyy, we will see "january 2021" appear in cell A2.
A nice stroke of luck is that we don't have to edit the year in the DATE formula. For Excel, a thirteenth month is nothing more than the month of January in the following year.
Formula 2: the sheet name
Now that we have filled our table with the months – or also with our worksheet names – we can use these cells to get the data from the corresponding worksheets. It is important here that our data (the sales figure) is of course in the same cell on every sheet.
1=INDIRECT("'"&[@Month]&"'!H2")
Now when we add a new line to our table, the formulas will be copied and we get the following result:
