excel circular reference

Posted by Dave Dimick on June 12, 2001 2:24 PM

I'd like to set up a converter in two cells of a spreadsheet. You enter the value in either cell (e.g., feet or inches), and the result (i.e., inches or feet) is displayed in the other cell. Is this possible?

Posted by Damon Ostrander on June 12, 2001 3:33 PM

Hi Dave,

This can be done quite nicely using the Change event for that worksheet. If for example the value in feet was in cell C5 and inches in C6 the code in the change event routine would simply be

If Not Intersect(Target,[C5])) Is Nothing Then
' Value entered in feet cell
[C6] = [C5] * 12
ElseIf Not Intersect(Target,[C6]) Is Nothing Then
' Value entered in inches cell
[C5] = [C6] / 12
End If

There are no formulas in these cells, so no circular references. If any other cell on the worksheet is changed this routine does nothing.

Happy computing.

Damon