I am trying to create a maintenance log for various pieces of equipment, where there is a sheet with a detailed log for each piece, and an overview sheet with some basic info. I have a macro set up to create a new sheet and enter the necessary data from a userform. I am also trying to get this macro to insert a new row on the overview sheet with references to this new equipment sheet on the overview with a formula like =’sheetname’!cell. The problem is the formula is being placed in the cell using a variable, so every time I add a new sheet, every instance of the formula updates to reference the newest sheet. How can I input my sheet name variable as just text, or otherwise break the link between the formula and variable?

For reference, here is the code I am using to try and create the reference.

Dim nom as String

Sheets("Overview").Cells(r, 2).Value = "='" & nom & "'!$E$1"

nom is the new sheet name, which is defined by the userform input.

I have tried things like nom.value but I get an invalid qualifier error.