Formula referencing a text value of a different cell to complete formula? Possible?

[essential]

So I have a formula working as intended, but there are two variables every month that will change and will require the formula to be updated to work.

This is the formula I have working as I want right now:
=SUMIF('2023 10'!B6:B3296, "Client 1", '2023 10'!M6:M3296)

The month tab will change every month (2023 10) and the number of rows will be different every month (3296)

Can I have the value 2023 10 in A1, and then my formula reference A1 and use the text value of A1 in the formula? I'm looking to only have to update A1/B1 each month to the new tab and row total so then all the formulas auto update to use those values, instead of updating each formula itself.

A1 = 2023 10
B1 = 3296
=SUMIF('A1'!B6:B'B1', "Client 1", 'A1'!M6:M'B1')

But it's using the text value of A1/B1, which would be my changing month and row total, to complete the formula.

Is something like this possible? If not I will continue using Find/Replace All.

Thanks.

 

[MountainFog]

It is possible using the INDIRECT function, however you must be careful with it, as it is a volatile function (it updates every time a volatile action is performed such as inserting new rows, hiding/unhiding lines, etc.) If this function is going to be used in hundreds of cells, I would find a different way to go about the solution, but you can always try implementing it and if you notice any slowdown of your workbook, change back.

=SUMIF(INDIRECT("'"&A1&"'!B6:B"&B1), "Client 1", INDIRECT("'"&A1&"'!M6:M"&B1))

 

[Joe4]

If you are building range reference dynamically, you will need to use the INDIRECT function, i.e.
this part:

'A1'!B6:B'B1'

would need to be replaced by this:

INDIRECT("'" & A1 & "'!B6:B" & B1)

To make sure you have it working right, you know what you want to build, i.e. this part:

'2023 10'!B6:B3296

so first build it as a string like this:

="'" & A1 & "'!B6:B" & B1

and then when you have it looking exactly right, surround it in the INDIRECT function.
Then you can replace the range in your formula with that.

Since you have two different dynamic ranges in your formula, you will need to apply the same logic to the other range too.
I will leave that one to you, to test your skills with this new knowledge!

 

[essential]

I will try these INDIRECT suggestions and see if I can get it working as intended. Thanks!

 

[essential]

I got it working. This was my final formula, except I'm using N1 & O1 instead of A1 & B1 till I reformat my sheet.

=SUMIF(INDIRECT("'" & N1 & "'!B6:B" & O1), "Client 1", (INDIRECT("'" & N1 & "'!M6:M: & O1)

Now I just have to see how well it holds up going forward. Thanks Fog & Joe.

 

[essential]

I pasted the wrong formula but I can't seem to edit that post. This is the working one:
=SUMIF(INDIRECT("'"&N1&"'!B6:B"&O1),"Client 1",(INDIRECT("'"&N1&"'!M6:M"&O1)))