convert cell value into name either to call a tab or a named array

[Dip_Bou]

To fill up a table, I need to gather values from multiple tabs; for each row a different tab.

I added the exact names of the tabs in the first column.

Then I tried =A10!B14 to get the value in the B14 cell of the A10-like named tab. But it does work because Excel sees A10 as a value and not as reference (tab name with the !)

Is there anyway to use the value of a cell as a reference, either for a tab name or an array name?

Many thanks for your help

[#2]

@Dip_Bou 

You can use the INDIRECT function:

=INDIRECT("'"&A10&"'!B14")

[#3]

@Dip_Bou You can do this using the INDIRECT function, but I recommend that you first reconsider your design. The way your data is spread across more than one worksheet makes your Excel work unnecessarily complicated. If all were on a single tab in a single table, it would be very easy to summarize your data.

The INDIRECT formula would look like this (the single quotes are needed because sheet names might contain spaces):

=INDIRECT("'"&A10&"'!B14")

[#4]

@HansVogelaar You beat me to it by 2 minutes 🙂

[#5]

@Dip_Bou 

In addition, if you drag above formula down it will return the value from B14 since it's fixed in the text. The workaround could be

=INDIRECT("'"&$A$10&"'!"&ADDRESS(ROW(B14), COLUMN(B14)))

[#6]

@HansVogelaar & @JKPieterse , thank you very much guys! Indirect function works to call a tab, indeed. 

However, I realized that the person who prepared the data basis did not exactly keep the same table format for the different tabs. Hence, I would to use the INDEX function and call an array instead of the the tab. Is that possible? 

=INDEX(INDIRECT(" ' "&A35&" ');MATCH($J$33;Inputs;0);MATCH(B$33;Quarterl;0))

The text in bold should call the array @Company1 in the name manager with cell A35 = Company1. 

It does not work though. Any thought on this, please?

[#7 · Accepted solution]

I just removed the quotes to read directly the cell value as a reference. It worked 🙂

=INDEX(INDIRECT(A35);MATCH($J$33;Inputs;0);MATCH(B$33;Quarter;0))

Unless I am mistaken, I guess we're done on this. Thanks folks!

[#8]

@Dip_Bou 

Is Company1 the name of the tab, or the name of the structured table, or the name of the range?