VBA to Change Cell References in Columns Based on String in Another Worksheet

Hello all, first time posting here and I’m a VBA novice so hopefully I can explain what I’m trying to do clearly….

I’m trying to change a cell reference in an entire column of a specific worksheet based on the value of a cell in another worksheet “BILL OF MATERIAL”. Here is the breakdown of what I’m trying to do:

“BILL OF MATERIAL” worksheet has a cell that has the address (example: “U5”) of the cell that has the name of the sheet (example “C5.10”) I want to make changes to. When I run a macro, I want the sheet with that name to have a whole column selected (always column “E”) and change the following reference in each cell, essentially shifting the reference one cell to the left:

Existing string:

=IF('BILL OF MATERIAL'!$U12>0,'BILL OF MATERIAL'!U12,"")

Change to:

=IF('BILL OF MATERIAL'!$U12>0,'BILL OF MATERIAL'!T12,"")