Tab names formula
Solved
Hello,
I have a table consisting of about ten sheets.
The sheets are named as follows:
Balance; Product 1; Product 2; Product 3; etc.
On my "Balance" sheet, I have the following table:
BALANCE
Cost 1; Cost 2; Cost 3; etc.
Product 1 1 formula that will look for my Cost 1 in the "Product 1" sheet
Product 2 1 formula that will look for my Cost 2 in the "Product 2" sheet
Product 3 1 formula that will look for my Cost 3 in the "Product 3" sheet
Etc.
My question is as follows:
If my formula looks like this =VLOOKUP($E$3,'Product 1'!$1:$1048576,3,FALSE), is it possible to replace the 'Product 1'! criterion with something more general in order to apply the formula more easily?
Because if I create a Product 4, I can't apply the formula without changing this criterion...
I hope I have been clear enough...
Thank you in advance for your time!
Configuration: Macintosh / Chrome 60.0.3112.90
I have a table consisting of about ten sheets.
The sheets are named as follows:
Balance; Product 1; Product 2; Product 3; etc.
On my "Balance" sheet, I have the following table:
BALANCE
Cost 1; Cost 2; Cost 3; etc.
Product 1 1 formula that will look for my Cost 1 in the "Product 1" sheet
Product 2 1 formula that will look for my Cost 2 in the "Product 2" sheet
Product 3 1 formula that will look for my Cost 3 in the "Product 3" sheet
Etc.
My question is as follows:
If my formula looks like this =VLOOKUP($E$3,'Product 1'!$1:$1048576,3,FALSE), is it possible to replace the 'Product 1'! criterion with something more general in order to apply the formula more easily?
Because if I create a Product 4, I can't apply the formula without changing this criterion...
I hope I have been clear enough...
Thank you in advance for your time!
Configuration: Macintosh / Chrome 60.0.3112.90
2 answers
-
Hello,
Maybe using the INDIRECT function?
Assuming the tab names are in column A, starting from cell A2.
And still assuming that the result to be obtained is in cell A25 of each tab.
This formula should allow you to achieve what you want:
=INDIRECT($A2"!$A$25")
To be incremented downwards...
Have a great day.
