Use sheet name extracted from a cell

Question

Use sheet name extracted from a cell

Navigation

#1 by (5 votes)

4

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 spreadsheets, and one of them can be replicated several times with several different names such as: Financial, Billing, Tax, etc., depending on the needs of each project.

One of my worksheets contains a reference to data that appears in these new worksheets through a formula. Below I will give the 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 manually enter the name of the worksheet it needs to look for, for example: Financial! A1: C50. What I would like is to be able to fetch the spreadsheet that takes the same column name, so it would be dynamic.

NOTE: The number of rows for each project in this summary worksheet is also variable, so you need to leave the formula picking up in the Table column in order to calculate.

Below the code in the following dots column:

=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))

excel

asked by anonymous 17.10.2014 / 14:32

1 answer

Dates Conversion to the Mysql database How to display a .txt file in a JtextArea automatically

score 5 · Accepted Answer

I finally found the solution thanks to a friend who pointed me to a #

link

I'll paste the reference text here just by adapting my question to contextualize:

Use the following function:

1) INDIRECT (text_ref; [a1]) - The indirect function,   represent a cell address, returning the value of this cell.

[a1] : Do not worry about this parameter, it's not necessary.

So if you write in any cell:

= INDIRECT (B1)

Assuming that in B1 it was written Financial! A2, in fact   would return the value of cell A2 of the 'Financial' worksheet. Already, if you   write:

= 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, rather than for   B1 we will write something that solves your problem.

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

In my case my formula is:

=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))