Excel 2007
<tbody>
[TD="align: center"]9
[/TD]
[TD="align: right"]15601
[/TD]
[TD="align: right"]7/31/2013
[/TD]
[TD="align: right"]50240
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]0006500136
[/TD]
[TD="align: right"]$36.92
[/TD]
[TD="align: right"]0006500136
[/TD]
[TD="align: right"]-19500297.24
[/TD]
</tbody>
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]F9
[/TH]
[TD="align: left"]='RAW DATA'!I9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]G9
[/TH]
[TD="align: left"]='RAW DATA'!P9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]H9
[/TH]
[TD="align: left"]='RAW DATA'!K9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]I9
[/TH]
[TD="align: left"]=REPLACE('RAW DATA'!A9,1,2,"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]J9
[/TH]
[TD="align: left"]=IF('RAW DATA'!C9="CS ","CAS", "NEED VALUE")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]K9
[/TH]
[TD="align: left"]='RAW DATA'!B9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]L9
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE),"NO MATCH FOUND")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]M9
[/TH]
[TD="align: left"]=IFERROR(INDEX(DAP!F$11:F$281,MATCH(REPLACE('RAW DATA'!A9,1,2,"")+0,DAP!A$11:A$281,0)),"NO PRICE LISTED")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]N9
[/TH]
[TD="align: left"]=VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]O9
[/TH]
[TD="align: left"]=(M9-N9)*K9
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Name
[/TH]
[TH="align: left"]Refers To
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]DAP
[/TH]
[TD="align: left"]=DAP!$A$11:$F$281
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]INSLOOKUP
[/TH]
[TD="align: left"]='Insurance to Contract'!$A$4:$E$36
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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.
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
red20 | redstocking | 6 garden st | green | ia | Y163694 | CAS |
<tbody>
[TD="align: center"]9
[/TD]
[TD="align: right"]15601
[/TD]
[TD="align: right"]7/31/2013
[/TD]
[TD="align: right"]50240
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]0006500136
[/TD]
[TD="align: right"]$36.92
[/TD]
[TD="align: right"]0006500136
[/TD]
[TD="align: right"]-19500297.24
[/TD]
</tbody>
Ross Data
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]F9
[/TH]
[TD="align: left"]='RAW DATA'!I9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]G9
[/TH]
[TD="align: left"]='RAW DATA'!P9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]H9
[/TH]
[TD="align: left"]='RAW DATA'!K9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]I9
[/TH]
[TD="align: left"]=REPLACE('RAW DATA'!A9,1,2,"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]J9
[/TH]
[TD="align: left"]=IF('RAW DATA'!C9="CS ","CAS", "NEED VALUE")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]K9
[/TH]
[TD="align: left"]='RAW DATA'!B9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]L9
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE),"NO MATCH FOUND")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]M9
[/TH]
[TD="align: left"]=IFERROR(INDEX(DAP!F$11:F$281,MATCH(REPLACE('RAW DATA'!A9,1,2,"")+0,DAP!A$11:A$281,0)),"NO PRICE LISTED")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]N9
[/TH]
[TD="align: left"]=VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]O9
[/TH]
[TD="align: left"]=(M9-N9)*K9
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Name
[/TH]
[TH="align: left"]Refers To
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]DAP
[/TH]
[TD="align: left"]=DAP!$A$11:$F$281
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]INSLOOKUP
[/TH]
[TD="align: left"]='Insurance to Contract'!$A$4:$E$36
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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.