I am working on a requisition data sheet which has an overview "register" with requisitions 001 to 255 allocated, along each line will be various criteria for the user to input. Once the user has input data on a line they then move over to a requisition template sheet (located within the same workbook), on here there is a macro recorded and a button assigned called 'duplicate template'; the user will press this button then move on to the newly created sheet and name it the same as the requisition line number (001). The user will then complete the requisition form to be submitted which includes a purchase value - my aim is for this purchase value (located always in J37) to back fill on to the 'register'. The formula works perfectly when the sheet is already present with the correct naming conventions but I am looking to set the formula within the template and then update the register once the sheet is created, all I am currently getting is #REF because sheet 001-255 does not yet exist.

If anyone has a solution I would appreciate it.