Adding one to the number from the previous cell with text...

[T.]

I homeschool my children and am in the process of setting up a grading book
for the year. I've got all my averages down, no problem. What I am having a
problem with is adding the lesson numbers or number/letter combination.
Instead of having to type each one in (Lesson 1, Lesson 2, etc) I would like
to have it auto populate the cells. When I type "Lesson 1" in the first
cell, I need it to fill in the "Lesson 2", "Lesson 3", etc.

With another book, the lessons are numbered and lettered. The first lesson
is 1A, 1B, 1C, then goes to 2A, 2B, 2C, etc.

Does anyone know how to make this happen?? I would appreciate any help
anyone can give... this is driving me nuts!!! LOL

Thanks so much!

 

[JLatham]

Second half of the problem first:

With 1A entered into cell A2, put this formula into cell A3 and fill it down
the sheet as far as you need:
=IF(RIGHT(A2,1)="C",VALUE(LEFT(A2,LEN(A2)-1))+1 &
"A",IF(RIGHT(A2,1)="A",LEFT(A2,1) &"B",LEFT(A2,1) & "C")

Remember that all of that is one formula, without an [enter] key in the
middle anywhere.

That should take care of your 1A, 1B, 1C, 2A, 2B, 2C ... problem.

Now for the Lesson 1... problem. If you have Lesson 1 in A2 and you have
Lesson 2 in A3, just select them both and then fill down, Excel is smart
enough to figure out that you want a series.

If your 1A, 1B, or Lesson # entries are spread out down the sheet, things
become a bit tougher. The solutions I've provided assume that each entry is

 

[bill kuunders]

I homeschool my children and am in the process of setting up a grading book
for the year.  I've got all my averages down, no problem.  What I am having a
problem with is adding the lesson numbers or number/letter combination.  
Instead of having to type each one in (Lesson 1, Lesson 2, etc) I would like
to have it auto populate the cells.  When I type "Lesson 1" in the first
cell, I need it to fill in the "Lesson 2", "Lesson 3", etc.  

With another book, the lessons are numbered and lettered.  The first lesson
is 1A, 1B, 1C, then goes to 2A, 2B, 2C, etc.

Does anyone know how to make this happen??  I would appreciate any help
anyone can give... this is driving me nuts!!!  LOL

Thanks so much!

For the Lesson 1 etc you can use the normal extend method
move the mouse pointer to the right hand bottom corner untill the
pointer changes to a "+" sign
left click and drag down
For the 2A 2B series it could be done with a help column and
concatenate and paste special values but it's probably quicker to just
type them in.
Greetings from New Zealand

 

[T.]

JLatham, thanks for the response. I am running this across the top of the
page, which is still working. I need it to go out to the letter "F" before
it starts on the next count. I have tried fixing this myself, but to no
avail. Can you tell me how to extend it out? This is what I was trying...

=IF(RIGHT(C1,1)="C",VALUE(LEFT(C1,LEN(C1)-1))+1&"A",IF(RIGHT(C1,1)="A",LEFT(C1,1)&"B",LEFT(C1,1)&"C",LEFT(C1,1)&"D",LEFT(C1,1)&"E",LEFT(C1,1)&"F")
)

Obviously, I have no idea what I'm doing! LOL
Thanks again so much for your help! This is going to save me tons of time!

 

[zvkmpw]

 I need it to go out to the letter "F" before it starts on the next count.

If I understand correctly, maybe this will help.

In A1 put
=(INT((COLUMN()-1)/6)+1)&CHAR(65+MOD(COLUMN()-1,6))
and copy rightward as far as needed.

 

[JLatham]

See if what zvkmpw offered works for you. If it does not, I need more sample
data just as it's laid out in your workbook. Your initial sample data showed
the entries going dow a row. But now you say "I am running this across the
top of the page..." and so I'm a bit confused as to the layout of your
worksheet now.

JLatham, thanks for the response. I am running this across the top of the
page, which is still working. I need it to go out to the letter "F" before
it starts on the next count. I have tried fixing this myself, but to no
avail. Can you tell me how to extend it out? This is what I was trying...

=IF(RIGHT(C1,1)="C",VALUE(LEFT(C1,LEN(C1)-1))+1&"A",IF(RIGHT(C1,1)="A",LEFT(C1,1)&"B",LEFT(C1,1)&"C",LEFT(C1,1)&"D",LEFT(C1,1)&"E",LEFT(C1,1)&"F")
)

Obviously, I have no idea what I'm doing! LOL
Thanks again so much for your help! This is going to save me tons of time!

Second half of the problem first:

With 1A entered into cell A2, put this formula into cell A3 and fill it down
the sheet as far as you need:
=IF(RIGHT(A2,1)="C",VALUE(LEFT(A2,LEN(A2)-1))+1 &
"A",IF(RIGHT(A2,1)="A",LEFT(A2,1) &"B",LEFT(A2,1) & "C")

Remember that all of that is one formula, without an [enter] key in the
middle anywhere.

That should take care of your 1A, 1B, 1C, 2A, 2B, 2C ... problem.

Now for the Lesson 1... problem. If you have Lesson 1 in A2 and you have
Lesson 2 in A3, just select them both and then fill down, Excel is smart
enough to figure out that you want a series.

If your 1A, 1B, or Lesson # entries are spread out down the sheet, things
become a bit tougher. The solutions I've provided assume that each entry is
on the row below the last previous entry.

 

[T.]

If I were starting in A1, it would absolutely fix it! However, I am starting
with C1 and going out to Z1. The list needs to read A1, B1, C1, D1, E1, F1,
A2, B2, etc.

You all are great for helping me and the formulas you've given me will help
me on other projects, so Thank you very much!

T.

 

[T.]

As much as I like what zvkmpw showed me, it's not fixing it. From cell C1 to
cell Z1 I need it fill in 1A, 1B, 1C, 1D, 1E, 1F, 2A, 2B, etc. Does that
make sense?

T.

See if what zvkmpw offered works for you. If it does not, I need more sample
data just as it's laid out in your workbook. Your initial sample data showed
the entries going dow a row. But now you say "I am running this across the
top of the page..." and so I'm a bit confused as to the layout of your
worksheet now.

JLatham, thanks for the response. I am running this across the top of the
page, which is still working. I need it to go out to the letter "F" before
it starts on the next count. I have tried fixing this myself, but to no
avail. Can you tell me how to extend it out? This is what I was trying...

=IF(RIGHT(C1,1)="C",VALUE(LEFT(C1,LEN(C1)-1))+1&"A",IF(RIGHT(C1,1)="A",LEFT(C1,1)&"B",LEFT(C1,1)&"C",LEFT(C1,1)&"D",LEFT(C1,1)&"E",LEFT(C1,1)&"F")
)

Obviously, I have no idea what I'm doing! LOL
Thanks again so much for your help! This is going to save me tons of time!

Second half of the problem first:

With 1A entered into cell A2, put this formula into cell A3 and fill it down
the sheet as far as you need:
=IF(RIGHT(A2,1)="C",VALUE(LEFT(A2,LEN(A2)-1))+1 &
"A",IF(RIGHT(A2,1)="A",LEFT(A2,1) &"B",LEFT(A2,1) & "C")

Remember that all of that is one formula, without an [enter] key in the
middle anywhere.

That should take care of your 1A, 1B, 1C, 2A, 2B, 2C ... problem.

Now for the Lesson 1... problem. If you have Lesson 1 in A2 and you have
Lesson 2 in A3, just select them both and then fill down, Excel is smart
enough to figure out that you want a series.

If your 1A, 1B, or Lesson # entries are spread out down the sheet, things
become a bit tougher. The solutions I've provided assume that each entry is
on the row below the last previous entry.

 

[T.]

OMG!! I just figured out how to alter your formula to make it work for me!!
Thank you so much for your help!! You've saved me a TON of time!!

T.