Pulling Info From Other Excel Sheets With Dynamic Cell Reference

[Sobrien9900]

Hello,

I am creating a tool that will verify some barcodes that we print from a .csv file. The tool works by comparing a barcode scan to the actual .csv file. Using an xlookup, I can put a fixed reference to the sheet with the .csv data. However, the .csv files are saved in a network file. I need a way to create a dynamic reference to the sheets in the folder on the network drive. The dynamic reference source would be in a fixed cell on the comparison tool sheet.

Cell in main sheet = concat of PN/LN

in this case = 701350233785

='S:\Manufacturing\Filling - Packaging\Filling\[701350233785.xlsx]701350233785'!F6

So, if I scan a new PN/LN 701255087568 in the main sheet reference cell, the references change to

='S:\Manufacturing\Filling - Packaging\Filling\[701255087568.xlsx]701255087568'!F6

Is there a way to accomplish this? The workbooks with the .csv will be closed, not open.

Thank you

 

[Anfinsen]

Hey there,
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCell As Range

' The variable KeyCell contains the cells that will
    ' update the forumla whem the cell is changed.
    Set KeyCell = Range("A1) 'set your Keycell here

If Not Application.Intersect(KeyCell, Range(Target.Address)) _
           Is Nothing Then


        ' Place your code here.
    Target.FormulaR1C1 = "='S:\Manufacturing\Filling - Packaging\Filling\[701255087568.xlsx]701255087568'!R[4]C[4]"

        MsgBox "Cell " & Target.Address & " has changed."
'delete the MsgBox line after testing

End If
End Sub

 

[Sobrien9900]

Appreciate the help. Tried the code and its not working for me.

Essentially I'm creating a mirror sheet from another workbook based on the file name. When I change the reference cell in the main sheet, the mirror sheet updates and pulls the info from the new file referenced. There might be another way to do this? Ive added some screens grabs that might better help explain. Im pretty novice at this. Any help is much appreciated.