Use sheet name extracted from a cell

Question

I am creating a workbook in excel that will serve as a template for many others that will be generated through it.

This template workbook has 5 worksheets, and one of it can be replicated several times with several different names like: Financial, Billing, Tax, etc, depending on the need of each project.

One of my spreadsheets contains a reference to data that appears in these new spreadsheets through a formula. Below I will give an example of how is my spreadsheet:

Títulos:
Nome 001
Nome 002
Nome 003

Tabela:
Financeiro
Faturamento
Tecnologia

Pontos:
115
168
985

The column of points today has a formula where I enter, manually, the name of the sheet that he needs to search for, for example: Financial! A1:C50. What I would like is to be able to search in the spreadsheet that takes the same name of the column, so it would be dynamic.

NOTE: The number of rows for each project in this summary sheet is also variable, so I need to leave the formula picking in the Table column to be able to calculate.

Below the column code following points:

=SE(ÉERROS(ÍNDICE(Modelo!$D$3:$BA$3;1;CORRESP($C5;Modelo!$D$3:$BA$3;0) + 2));"";ÍNDICE(Modelo!$D$3:$BA$3;1;CORRESP($C5;Modelo!$D$3:$BA$3;0) + 2))

Answer 1

I finally found the solution thanks to a friend who pointed out a link that helped him to solve the same problem of his. I will paste the text of the reference here just adapting my question to contextualize:

Use the following function:

1) INDIRECT(texto_ref; [a1]) - The indirect function, picks up texts that represent a cell address, returning the value of this cell.

[a1]: do not worry about this parameter, it is not necessary.

So if you write in any cell:

=INDIRECT(B1)

Assuming B1 is written Financial! A2, in fact return cell A2 value of 'Financial' spreadsheet. Already, if you write down:

=INDIRECT("B1")

The function recognizes B1 as a text that returns a cell, returning the value of cell B1. This is great for what you want instead of by B1 We will write something that will solve your problem.

Knowing that writing the symbol & joins two texts we can now join B1, where written Financial, with a written text ! A2, thus writing in any cell: =B1&"! A2". This will return the Financial text! A2. Writing inside the INDIRECT() function will return what you want, just write: =INDIRECT(B1&"! A2").

In my case my formula stays:

=SE(ÉERROS(ÍNDICE(INDIRETO(D5&"!$D$3:$BA$3");1;CORRESP($C5;INDIRETO(D5&"!$D$3:$BA$3");0) + 2));"";ÍNDICE(INDIRETO(D5&"!$D$3:$BA$3");1;CORRESP($C5;INDIRETO(D5&"!$D$3:$BA$3");0) + 2))