Function or VBA Help Needed

[FireGeek822]

I am trying to do some automation of cells and have an IF Function
started. As I continue to build this automation, I am wondering if a
VBA solution is better? Any help/suggestions would be appreciated.

Current function in all ColB cells starting at row B2 (B1 is empty on
purpose) is as follows:

=IF((A2)<>"", CONCATENATE((A2), ".", "1"), CONCATENATE((A2), ".",
(RIGHT((B2), (SEARCH(".",(B2),1))-1))+1))

This produces the following

ColA ColB
1
1.1
1.2
1.3
2 1.4
2.1
2.2
2.3
3 2.4
3.1
3.2
3.3
4 3.4
4.1

Ideally, we want all cells that now contain 1.4, 2.4, 3.4, etc to be
blank as with cell (B1). We would like ColB to be locked so no one can
edit this column. It should also update automatically if someone enters
a value in ColA.

Any thoughts/suggestions would be greatly appreciated.

Tammy
(e-mail address removed)

 

[JE McGimpsey]

One way:

B1: <blank>
B2: =IF(A2,"",MAX($A$1:A1)&"."&IF(B1="",1,MID(B1,FIND(".",B1)+1,15)+1))

Copy B2 down as far as necessary.

 

[FireGeek822]

Thanks for the help. Not sure what is going on in your formula and
would like to understand it if you could explain. Typically, I like to
understand what I am building vs. just using someone's code.

Tammy
(e-mail address removed)

 

[JE McGimpsey]

Breaking it down:

B2: =IF(A2,"",MAX($A$1:A1)&"."&IF(B1="",1,MID(B1,FIND(".",B1)+1,15)+1))


=IF(A2,""

if A2 is blank (or zero), then B2 will be blank. Otherwise

MAX($A$1:A1)

returns the largest value in column A up to the previous row. This
assumes that your numbers in column A are in ascending order, which
seems likely given your example.

&"."

append a decimal point.

&IF(B1="",1

If the cell above is blank (because the column A cell holds a value),
then append 1 to start the sequence, otherwise

MID(B1,FIND(".",B1)+1, 15)+1

Find the value to the right of the decimal point in the previous cell,
add 1, and append it to the "x."

 

[FireGeek822]

Thanks for such a thorough explanation. I should have been more clear
- wasn't sure about the MAX portion of your statement.

THANKS anyway - for the newbies out there looking at this post.

Tammy