Refer to a cell in another worksheet by referencing the current worksheet s name

Question

I plan to have a workbook with 24 sheets   The sheet names will be  Jan  Jan item  Feb  Feb item  etc   Basically it s for budgeting  with the month named sheet having summary info  and the ones with  item  in the name having more detailed info   Now  if I want the  Jan  spreadsheet to have a cell referring to cell J3 in the  Jan item  spreadsheet  I can do the following     Jan item  J3  However  instead of having to re-enter the formulas like this for each summary sheet  Jan  Feb  etc   I would like to have something like     reference-to-this-sheet s-name     item   J3  That way  I can copy the formula from Jan to Feb  and it will automatically look at the correct Xxx item sheet without me having to type in Feb item explicitly   The reason I ask this is because  for each summary sheet  there will be dozens of such references to a cell in the corresponding itemized sheet   Is what I m asking doable

User · Answer

Here is how I made monthly page in similar manner as Fernando:

I wrote manually on each page number of the month and named that place as ThisMonth. Note that you can do this only before you make copies of the sheet. After copying Excel doesn't allow you to use same name, but with sheet copy it does it still. This solution works also without naming.
I added number of weeks in the month to location C12. Naming is fine also.
I made five weeks on every page and on fifth week I made function
```
  =IF(C12=5,DATE(YEAR(B48),MONTH(B48),DAY(B48)+7),"")
```
that empties fifth week if this month has only four weeks. C12 holds the number of weeks.
...
I created annual Excel, so I had 12 sheets in it: one for each month. In this example name of the sheet is "Month". Note that this solutions works also with the ODS file standard except you need to change all spaces as "_" characters.
I renamed first "Month" sheet as "Month (1)" so it follows the same naming principle. You could also name it as "Month1" if you wish, but "January" would require a bit more work.
Insert following function on the first day field starting sheet #2:
```
 =INDIRECT(CONCATENATE("'Month (",ThisMonth-1,")'!B15"))+INDIRECT(CONCATENATE("'Month (",ThisMonth-1,")'!C12"))*7
```
So in another word, if you fill four or five weeks on the previous sheet, this calculates date correctly and continues from correct date.

User · Answer

Still using indirect   Say your A1 cell is your variable that will contain the name of the referenced sheet  Jan   If you go by    INDIRECT CONCATENATE     A1   Item      J3     Then you will have the  Jan Item  J3 value

User · Answer

Unless you want to go the VBA route to work out the Tab name  the Excel formula is fairly ugly based upon Mid functions  etc  But both these methods can be found here if you want to go that way   Rather  the way I would do it is   1  Make one cell on your sheet named  for example  Reference Sheet and put in that cell the value  Jan Item  for example   2  Now  use the Indirect function like    INDIRECT Reference Sheet amp   J3      3  Now  for each month s sheet  you just have to change that one Reference Sheet cell   Hope this gives you what you re looking for

[excel] Refer to a cell in another worksheet by referencing the current worksheet's name?

Examples related to excel

Examples related to excel-formula

Examples related to worksheet-function