part of path imported from cell value

Hi,
I would like bolded part of following path to be imported from N5 cell. Can you help to provide me with relevant formula?

='J:\ISO\01_ISO\07_Audity wewnetrzne\2015\Karty niezgodnosci\[NCR_FINACI_20150115_1200 - Etap 1.xlsm]DCT_ISOFOR_SC_8.02_NCR'!$I$8



Regards,
Marcin

Hi,

Welcome to the Forum.

Please use the following formula:

=MID(N5,FIND("[",N5,1)+1,(FIND("]",N5,1)-FIND("[",N5,1)-1))

Thank you for your replies.
It seems, I expressed my intentions in wrong way.



So assuming that N5 = NCR_FINACI_20150115_1200 - Etap 1.xlsm

I would like to keep:
='J:\ISO\01_ISO\07_Audity wewnetrzne\2015\Karty niezgodnosci\[&=N5&]DCT_ISOFOR_SC_8.02_NCR'!$I$8

Instead of:
='J:\ISO\01_ISO\07_Audity wewnetrzne\2015\Karty niezgodnosci\[NCR_FINACI_20150115_1200 - Etap 1.xlsm]DCT_ISOFOR_SC_8.02_NCR'!$I$8

It seemed to be easy, but I spent hours trying to solve this issue

I have tried in this way as well, but unfortunately it does not work (#REF!).
Is there any other funtion that might be used?

Originally Posted by Marcin4111

It might be easier to find a solution with attached document. For simulation it is required to determine the path.
Attachment 324631
Formula is required to cell D5

Regards,
Marcin

Guys,
Do you have any idea how to solve it?

Path is correct, but referenced workbook is closed and that's the problem... INDIRECT does not work for closed workbooks. It works with INDEX/MATCH, but there I couldn't provide with dynamic filename.

I tried with macro, so with google I developed something that forks for individual cell:
Sub PullValue()
Dim PATH, FILENAME, SHEETNAME, CELL

PATH = "J:\ISO\01_ISO\07_Audity wewnętrzne\2015\Karty niezgodności\"
FILENAME = Range("M5")
SHEETNAME = "DCT_ISOFOR_SC_9.02_2015"
CELL = "I8"

With Range("C5")
.Formula = "='" & PATH & "[" & FILENAME & "]" & SHEETNAME & "'!" & CELL & ""
.Value = .Value
End With

End Sub


I tried to develope macro that works dynamically for all column, so what I wish:
Sub PullValue()
Dim PATH, FILENAME, SHEETNAME, CELL

PATH = "J:\ISO\01_ISO\07_Audity wewnętrzne\2015\Karty niezgodności\"
FILENAME = Range("M5")
SHEETNAME = "DCT_ISOFOR_SC_9.02_2015"
CELL = "I8"

With Range("C5")
.Formula = "='" & PATH & "[" & FILENAME'(M5:M204 switched for relevant range C5:C204) & "]" & SHEETNAME & "'!" & CELL & ""
.Value = .Value
End With

End Sub


So the only dynamic should be FILENAME.
Could you please to provide me with modified macro?

Regards,

Excactly, assuming:

There are 4 factors required for simulation:
PATH - static
FILENAME - dynamic required
SHEET - static
CELL - static

Dynamic values for FILENAME are defined in Range("M5:M204")

Formula should cover Range("C5:C204")
.Formula = "='" & PATH & "[" & FILENAME 'dynamic values & "]" & SHEETNAME & "'!" & CELL & ""

Finally solved! Thanks for advices

Sub PullValue_daniel()
Dim PATH, FILENAME, SHEETNAME, CELL
Dim wrs As Integer

PATH = "J:\ISO\01_ISO\07_Audity wewnętrzne\2015\Karty niezgodności\"
SHEETNAME = "DCT_ISOFOR_SC_9.02_2015"
CELL = "I8"

wrs = 5

Do While Cells(wrs, 13) <> ""
FILENAME = Range("M" & wrs)

With Range("C" & wrs)
.Formula = "='" & PATH & "[" & FILENAME & "]" & SHEETNAME & "'!" & CELL & ""
.Value = .Value
End With

wrs = wrs + 1
Loop

End Sub