Trying to use a cell to refference a workbook.

[porkchop101]

Hi I have workbooks which are numbered numerically 1-600 am trying to use a cell in a different workbook which will reference on of those 600 workbooks and bring data from each one individualy.
I have been tring to use this formula but I think its not allowing me to add the .xls please help.
=INDIRECT("\\gammanas\gamma\Job Cost\Gamma\GAMMA 2015\JOB COST\"&E18".xls"&"!C38")

[TMS]

Try (untested):

Please Login or Register  to view this content.

And you might need to add some single quotes in there to cater for spaces in the workbook and/or worksheet name.


Regards, TMS

[porkchop101]

Thanks for the fast reply however I get #ref error in the formula the E18 refers to a cell with 108 in it which I need to refer to 108.xls with the path I have provided.

[porkchop101]

Wat I think I found the problem I havn't refered to a sheet name, how would I include that in the Formula.

[TMS]

I think you need to be aiming for the formula to evaluate to:

Please Login or Register  to view this content.

so, on that basis, try:

Please Login or Register  to view this content.

Note the square brackets around the workbook name (as opposed to the full path).

If it doesn't work with the target workbook closed, try opening it and refreshing the formula.

Regards, TMS

[porkchop101]

Thanks again for the fast reply. The first formula without indirect is working and is bringing across the value.But the formula with indirect is still giving #ref error.
Which is still a problem because I need it to automatically take and use the job number to find the excel workbook.

[porkchop101]

Unless im using the wrong function to do this.

[porkchop101]

Ok I got it working is there any way it can update without me opening up the other workbook otherwise it gives me #ref error.