Help with an Indirect formula

[gheyman]

I have this in cell B10: R:\SUPPLY CHAIN MANAGEMENT\SC Data Analysis Group\RFQ Process\NIS RFQ Template\Example 02-RFQ R1374334AH COMPULINK INC. (10V001285) 231207 220857 2.xlsb

I want to create an Indirect formula using the above to get the value in cell B4 on the tab named "Main"

The above in B10 is a dynamic value that will change. This is why I want to us an indirect formula. The workbook name and location is dynamic but the workbooks will always have a tab named "Main"

Thanks

 

[MountainFog]

=INDIRECT("["&B10&"]Main!$B$4")

 

[Fluff]

You cannot use Indirect on closed workbooks

 

[Fluff]

@MountainFog That is never going to work given the OP has the full filepath in B10

 

[gheyman]

Thanks, Fluff. So what I am trying to do is not possible? You are correct that the workbook would be closed.

 

[Fluff]

Fraid not. The only way to use a cell value as the name of a workbook/sheet is to use indirect.

 

[gheyman]

So could it be done without an indirect? Maybe concatenating the values?

 

[Fluff]

No, that's why I said it could only be done using indirect.