Hey Folks,

I'm revamping our sample tracking log to be a little more user friendly, but it's not being friendly to me.

Using the sample sheet attached this is what I'm trying to do.

I have a range of cells in column H, "SRN Link" (H2:H7) formatted to take values from the corresponding rows in column A, "Lab Work Order #", because we want the value to be the same. I used the basic formula =$A2.

We would eventually add a hyperlink to the value in "SRN Link" from a folder we keep our SRN docs in on SharePoint.

I would like to be able to lock the range of cells in column H so that the formula isn't accidentally deleted by summer students, or someone not paying attention, but be able to add a hyperlink to the locked cell once we receive our SRN doc for that corresponding work order.

I know how to lock the cells, protect the sheet etc. I've tried Data Validation and some VBA code, but can't seem to figure it out. I've tried other ways to auto populate the values from column A to column H with no luck. Any ideas? Is this possible?

Attached is a sample worksheet, TIA!

Cheers,
EnviroTechSample Work Book - Lab Sample Tracking Log.xlsx