Incorrect formula references when looking up cells in renamed Sheets

I can understand about not wanting to post a screenshot, although it would help (even if you obfuscated the data). The problem is your description and examples are confusing, and maybe it's just a terminology thing.

In Numbers, everything lives inside a Document. Each Document contains one or more Sheets. Each Sheet can contain any number of Tables. Tables consist of a number of rows and columns.

To Numbers, the formula:

Period 1::D3:D38

means the range of cells D3 through D38 on the Table named Period 1

In your description you imply that 'Period 1' is a sheet, not a table.

If your table names are unique (i.e. there is only one table called 'Period 1' in all the sheets in the current document), then 'Period 1::D3:D38' would find those cells on that table.

If multiple sheets have a table with the same name then you need one more level of indirection to tell Numbers which one you want (otherwise it will choose the first one it finds, starting with the current sheet)

Sheet Name::Period 1::D3:D38

will force a lookup to the 'Period 1' table on the 'Sheet Name' sheet.

Since you only mention one level of indirection, that either means all your table names are unique, or it's referencing the named table on the current sheet, and not referencing a different sheet at all.

If you have a table named "Month" and it is the only table in your entire document with that name, cell references to that table will not include the sheet name.

My first thought here is that the table you are referencing in your formula is named "Month" and it is the only table of that name in your entire document. If you want the sheet name to be part of the cell reference, duplicate the sheet so you have two or more sheets with a table named "Month". The cell references will then include the sheet name. Sheetname::tablename::cell/range

Edit: I see Camelot already addressed this

What happens when, instead of typing in the formula, you open the formula editor then point and click and let Numbers fill in the correct range references, instead of you typing them in?

If you have structured your Numbers document well (which we can't see because of lack of screenshots) and are taking advantage of Footer Rows, then you are more likely to have references to D and E rather than D1:D38 and E1:E38.

From what little we can see, this looks similar to the 'Personal Budget' template at File > New in the menu. If you haven't done so already, then I highly recommend having a look at it. If you have more than one period then you may find this extension of that template useful:

Budget Template Expanded (Dropbox download).

SG

Can you take a screenshot or two o we can see what is going on? A screenshot showing the formula and the tabs of the other sheets (so we can see the names of the sheets) will hopefully be useful. I have never seen a formula not update a cell/range reference when a table or sheet is renamed.

Command Shift 4 then drag a rectangle and let go to create a screenshot. Use the "mountain and moon" icon here to put it in a post.

It helps to now see the actual formula. You say the formula is giving the correct result, getting its data from the Transactions table in the Period 1 sheet even though the formula has the sheet name as Month. You have no sheet named Month with a Transactions table in it or, if you do, the formula is not getting the data from there. If all that is the case, I have never seen that happen before and have no idea why it is doing it or what has gone wrong.

If you click on an empty place on the Period 1 sheet and look at the format sidebar on the right, does it have the sheet name as Period 1 or Month?

What happens if you change the sheet name to something else? Maybe that will clear it up then you can change back to Period 1.

If you have a sheet named Month, what happens if you change its name?