Referencing data from another sheet by formulating the sheet name by joining data from multiple cells

[QuestionBaker]

I have multiple sheets where my target data is computed, let the sheet names be as follows
Sheet1, Sheet2, Sheet3, Sheet4 and so on...
I want the data in Sheet0
I can figure out which sheet I should be looking checking 3 cells
1st cell gives the first 3 letters, next cell gives next 2 letters and last cell gives me the number data
To get data from another sheet the following formula comes up in the formula bar
='Sheet1'!C1
Is it possible to construct first part of the formula, and just drag the formula, something like this
=TEXTJOIN("", , "='"A1, A2, A3, "'!", "C", "1")
The above formula just prints the following in the cell
='Sheet1'!C1

 

[Legacy 143009]

Yes, INDIRECT function is what you are looking for. Let say in A1 = "She", B1 = "et", C1 = "0", D1 = "E1", E1 = "Your formula works"
Try this formula in any cell F1:

=INDIRECT("'"&A1&B1&C1&"'!"&D1)

Remember that INDIRECT function is not advisable because it is a nonvolatile function which means, if you write an address in strings such as INDIRECT("'Sheet0'!C1") it won't increment row numbers such as 1,2,3, etc. when you drag which may cause inconsistency in formulas.

 

[QuestionBaker]

Yes, INDIRECT function is what you are looking for. Let say in A1 = "She", B1 = "et", C1 = "0", D1 = "E1", E1 = "Your formula works"
Try this formula in any cell F1:

=INDIRECT("'"&A1&B1&C1&"'!"&D1)

Remember that INDIRECT function is not advisable because it is a nonvolatile function which means, if you write an address in strings such as INDIRECT("'Sheet0'!C1") it won't increment row numbers such as 1,2,3, etc. when you drag which may cause inconsistency in formulas.

It works, only had to make the following changes

=INDIRECT("'"&A1&B1&C1&"'!"&D$1&"1")

I ended up storing the letter D in a different cell and 1 as a constant, and carefully using $, I was able to drag the formula
If the cell is always constant, following formula can be used

=INDIRECT("'"&A1&B1&C1&"'!"&"D1")

My final formula looks like this

=INDIRECT("'"&$A6&" "&$B$2&" "&$B6&"'!"&H$1&"1")

 

[Legacy 143009]

Ok, thanks for the deatiled feedback Gald to see it worked!