Using formula output as arguments in a Vlookup

[rogihead]

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
9	red20	redstocking	6 garden st	green	ia	15601	Y163694	7/31/2013	50240	CAS	3	0006500136	$36.92	0006500136	-19500297.24

<tbody>
</tbody>

Ross Data​

Worksheet Formulas Cell Formula F9 ='RAW DATA'!I9 G9 ='RAW DATA'!P9 H9 ='RAW DATA'!K9 I9 =REPLACE('RAW DATA'!A9,1,2,"") J9 =IF('RAW DATA'!C9="CS ","CAS", "NEED VALUE") K9 ='RAW DATA'!B9 L9 =IFERROR(VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE),"NO MATCH FOUND") M9 =IFERROR(INDEX(DAP!F$11:F$281,MATCH(REPLACE('RAW DATA'!A9,1,2,"")+0,DAP!A$11:A$281,0)),"NO PRICE LISTED") N9 =VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE) O9 =(M9-N9)*K9 <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To DAP =DAP!$A$11:$F$281 INSLOOKUP ='Insurance to Contract'!$A$4:$E$36 <tbody> </tbody>

<tbody>
</tbody>

I have another issue with the Vlookup and using the input from another cell as an argument in the formula. For this one I need to have column N to do a vlookup based of the item number in column I. The issue I have is that the contract number in column L will have its own worksheet to refer to for that specific item number. So in this case I need to look for item number 50240 on sheet 0006500136 but both of those cells are generated by formulas. So the issue I have is adding this information as arguments to the formula in N. Then in the last cell O there is one final calculation that needs to be completed, but once the cells M and N are populated I believe they will be calculated. I know that I can get the value of the sheet by using ="'"&INDIRECT("L9")&"'!" but I cannot seem to put that together with the array value and get a working formula. have also tried =VLOOKUP(I9,INDIRECT("'" & L9 & "'!$B$6:$C$23"),2,0) for N but that gives me an #REF error. Probably because the value for L9 is a result of a formula. SO I have reviewed many sites and a Vlookup book I downloaded from Mr.Excel but I do not see where this is touched on.