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!
7 Replies
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)))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")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?
