Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
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." wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
On Oct 14, 8:24*am, T. wrote:
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
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(C 1,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! "JLatham" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
*I need it to go out to the letter "F" before it starts on the next count.
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
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. "T." wrote: 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(C 1,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! "JLatham" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
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. "zvkmpw" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
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. "JLatham" wrote: 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. "T." wrote: 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(C 1,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! "JLatham" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding one to the number from the previous cell with text...
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. "zvkmpw" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding text without erasing previous text? | Excel Discussion (Misc queries) | |||
Adding calculated value to previous cell | Excel Discussion (Misc queries) | |||
Automate adding in a reference number if the adjacent cell contains text... | Excel Worksheet Functions | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
formatting cell number based on previous cell number | Excel Discussion (Misc queries) |