Not sure how to reference a path.

[DeanRobinson]

Hi all, ive got a yearly holiday register that i want to take information directly off of different departments daily registers.

what id like to do is use a cell reference to put in the file location of the sheet i need.

ive tried the formula

="'"&INDIRECT("B12")&" "&INDIRECT("a252")&INDIRECT("B11")

which returns the below.
<TABLE style="WIDTH: 449pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=598><COLGROUP><COL style="WIDTH: 449pt; mso-width-source: userset; mso-width-alt: 21869" width=598><TBODY><TR style="HEIGHT: 24pt" height=32><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 449pt; HEIGHT: 24pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=32 width=598>'[SL Attendance Register Aug.xls]SL1'!$C$5:$AG$100

now this works in my formula if its typed in but i dont want it hard coded into the formula, the full formula is

=IF(IF(ISERROR(VLOOKUP(I$10,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$C$5:$AG$65,'[SL Attendance Register NOV.xls]SL2'!$C$5:$AG$65),MATCH($E17,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$D$4:$AH$4,'[SL Attendance Register NOV.xls]SL2'!$D$4:$AH$4))+1,0)),"",(VLOOKUP(I$10,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$C$5:$AG$65,'[SL Attendance Register NOV.xls]SL2'!$C$5:$AG$65),MATCH($E17,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$D$4:$AH$4,'[SL Attendance Register NOV.xls]SL2'!$D$4:$AH$4))+1,0)))=0,"",(IF(ISERROR(VLOOKUP(I$10,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$C$5:$AG$65,'[SL Attendance Register NOV.xls]SL2'!$C$5:$AG$65),MATCH($E17,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$D$4:$AH$4,'[SL Attendance Register NOV.xls]SL2'!$D$4:$AH$4))+1,0)),"",(VLOOKUP(I$10,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$C$5:$AG$65,'[SL Attendance Register NOV.xls]SL2'!$C$5:$AG$65),MATCH($E17,CHOOSE(MID(I$11,3,1),'[SL Attendance Register NOV.xls]SL1'!$D$4:$AH$4,'[SL Attendance Register NOV.xls]SL2'!$D$4:$AH$4))+1,0)))))

which im trying to shorten.


</TD></TR></TBODY></TABLE>

 

[DeanRobinson]

Its ok ive worked it out.