Jill
07-06-2017, 11:19 PM
Hi.
I am using office 2010. I'm new to coding and would appreciate help from any excel guru that is willing to help a newbie.
I am trying to debug a worksheet I've created. Its super complicated but appears to work with one exception, as far as i can tell, the index/match works from .005-.95. As soon as i reach 1.0, the macro does not return a value.
In the attached example, ThreeStone.xls, the issue is with Sheet1, column L. In the attached sheet you will see that L6 & L7 have a value of .4. This works as expected.
However, based on the formula in column L I would expect L4 to have a value of 1.75, but instead it gets #REF!
The formula is as follows
=INDEX(RBC!D2:D94,MATCH("Diamond_Round"&Sheet1!I4,E4&RBC!B2:RBC!B96,1))
The formula references the RBC worksheet which gives a range in which the value could fall. If it falls within that range it should return a value in the Carat column.
To give a bit of background info, this worksheet is automatically generated from another program. Essentially, I open a CAD file, process the CAD, pulling info and inputting that info into a Master excel sheet. This is the result. As you can see, there are a lot of empty rows. This is because each CAD file is different and could have anywhere from 1 row of info to 80 rows of info.
I'm also attaching a file called ThreeStoneWorking.xls. This is the same CAD except with a smaller stone (less than 1.0CT). In this example it works! See Sheet1 Column L. Like I said as far as i can tell, the macro works when Sheet1, I4 is less than 6.5 (mm) which is less than 1.0 CT. As soon as it is above, the macro fails.
Any ideas what Im doing wrong? Many thanks in advance.
Jill
I am using office 2010. I'm new to coding and would appreciate help from any excel guru that is willing to help a newbie.
I am trying to debug a worksheet I've created. Its super complicated but appears to work with one exception, as far as i can tell, the index/match works from .005-.95. As soon as i reach 1.0, the macro does not return a value.
In the attached example, ThreeStone.xls, the issue is with Sheet1, column L. In the attached sheet you will see that L6 & L7 have a value of .4. This works as expected.
However, based on the formula in column L I would expect L4 to have a value of 1.75, but instead it gets #REF!
The formula is as follows
=INDEX(RBC!D2:D94,MATCH("Diamond_Round"&Sheet1!I4,E4&RBC!B2:RBC!B96,1))
The formula references the RBC worksheet which gives a range in which the value could fall. If it falls within that range it should return a value in the Carat column.
To give a bit of background info, this worksheet is automatically generated from another program. Essentially, I open a CAD file, process the CAD, pulling info and inputting that info into a Master excel sheet. This is the result. As you can see, there are a lot of empty rows. This is because each CAD file is different and could have anywhere from 1 row of info to 80 rows of info.
I'm also attaching a file called ThreeStoneWorking.xls. This is the same CAD except with a smaller stone (less than 1.0CT). In this example it works! See Sheet1 Column L. Like I said as far as i can tell, the macro works when Sheet1, I4 is less than 6.5 (mm) which is less than 1.0 CT. As soon as it is above, the macro fails.
Any ideas what Im doing wrong? Many thanks in advance.
Jill