Requirement: column A in Excel contains a data validation drop-down menu which contains numbers from 1 to as many Objectives that have been inserted in another field somewhere. The requirement in column B is to create a numbered sequence via a formula which will automatically calculate in the form 1.2, 1.2, 1.3, 2.1, 2.2 etc. no matter how many numbers there are. This can be done in the following way:

=IF(OFFSET($B10,0,-1,1,1),IF(OFFSET($B10,0,-1,1,1)=

OFFSET($B10,-1,-1,1,1),$A10&”.”&

RIGHT(OFFSET($B10,-1,0,1,1),1)+1,$A10&”.1″),””)

The formula assumes that the count begins in row 10 and that there are no more than 9 subsets for any principal number.

Advertisements