Here’s the problem, we have four separate tables with the same layout. They hold four different metrics: Actuals, Budget, Forecast and Last Year. A column called Type is used to hold the metric name. We need to populate the Type column.
An image of the data is below. The tables could easily be appended into a single table using Power Query.
These tables have been created using Format As Table in the Home ribbon. (There are links to learn more about Format As Table at the bottom of this post).
We need to populate the Type columns in each of the four sheets with one of the four metric names.
In the left hand sheet (Actuals) in the above image the user could enter the word Actuals in cell E2 in the Type column and then manually copy it down.
Or we could enter the following formula in cell E2 and press Enter.
="Actuals"
This will automatically copy the formula down – see image below.
And because it is a formula it will automatically extend whenever new data is added to the bottom of the table – see below where a new date was entered in A16.
This removes a manual process for the user.
We can create three other separate formulas for the three other sheets to enter their respective metrics.
Because the sheet names are also the metric names, you could enter the formula below in each of the four E2 cells. This formula extracts the sheet name. It is quite complex.
=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-SEARCH("]",CELL("filename",$A$1)))
If you want learn how this formula works check out this post which discusses it.
If you want to learn more about Formatted Tables see the following two blog posts.
Format as Table Part 2 – Video
Please note: I reserve the right to delete comments that are offensive or off-topic.