Reference a different sheet in an excel document who's name is in a separate cell.

Question

I have an excel workbook that has a home page, and a template page that I made, and then for every month, I have copied my template page and renamed it to the month and year in the mmmm yyyy format. So basically I want to get the values for two columns on the home page from two other columns on another sheet on the same excel workbook, except I want them to automatically update based on what month it currently is, so I made a formula that comes up with the name of the sheet for this month and the name of the sheet for last month, I then made a formula that I thought would pull the cells I want from the sheet I want: "='J2'!B3:B13", but that didn't work because there isn't a sheet actually called "J2". Is there another formula that I could use for J3 and L3? Thank you!!!

I keep getting errors when I try to upload my screenshots, and I guess instead of trying to figure out why that is, I will just recreate the section of the home sheet here.

	J	K	L
2	=TODAY() [displays "December 2024" when cell is not selected]		=EDATE(J2,-1) [displays "November 2024" when cell is not selected]
3	='J2'!B3:B13 [displays "#REF!" when cell is not selected]		='L2'!B3:B13 [displays "#REF!" when cell is not selected]
4	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
5	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
6	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
7	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
8	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
9	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
10	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
11	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
12	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
13	[displays "#REF!" when cell is not selected]		[displays "#REF!" when cell is not selected]
14	=SUM(J3:J13) [displays "#REF!" when cell is not selected]		=SUM(L3:L13) [displays "#REF!" when cell is not selected]
15	=F15-J14 [displays "#REF!" when cell is not selected]		=F15-L14 [displays "#REF!" when cell is not selected]

Answer 1

Thank you so much! That worked perfectly!

Answer 2

I'm sorry,I overlooked that J2 contains a number but your sheet is named "December 2024".

Try

=INDIRECT("'"&TEXT(J2,"mmmm yyyy")&"'!B3:B13")

If that doesn't work I need to see your file.

Andreas.

Answer 3

That didn't work. The only thing that appeared to change was that the "#REF!" error message only appears in J3 (and not in J4:J13 like it did before).

Thanks anyway!

Answer 4

I then made a formula that I thought would pull the cells I want from the sheet I want: "='J2'!B3:B13",

Try

=INDIRECT("'"&J2&"'!B3:B13")

Andreas.