Forum Discussion
convert cell value into name either to call a tab or a named array
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
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!
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)))HansVogelaar You beat me to it by 2 minutes 🙂
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?
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")
