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

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&"!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!

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

[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