Excel help

Hi

Another one of those excel questions, this one probably not that difficult.

What I want to do is a list of a letter, followed by a number that increases by one for each row, like this:
A1
A2
A3
etc…

I know how to do it when there is only the number, but that letter messes it up for me. Help please? :slight_smile:

The one way I know to do it is with a hidden column. It needs to be straight numbers. The actual showing column should refer to that column, and be set to =“A”&A1 and dragged down. (A1 just being the example if the hidden column is A)

Works beautifully. Thanks!

Also, depending on what you’re doing in the worksheet, perhaps this will work for you:
Put “A1” in the first row, “A2” in the next, and then highlight the two cells and drag down to populate the rest of the cells. This works for many types of series.

To increment from A1-A10, B1-B10, C1-C10 etc in column C

Cell A1 put A
Cell B1 put 1
Cell C1 =A1&B1
Cell A2 =IF(B2=10,CHAR(CODE(A2)+1),A2)
Cell B2 =IF(B1=10,1,B1+1)
Cell C2 =A2&B2

copy the formulas in row 2 as far down as required.
You can change to first character to any ASCII value, limit is CHAR(255)
If you want numbers to run to 25, or 100 or other value change formulas in A2 and B2

I typed A1 into cell A1, A2 into cell A2, and A3 into cell A3. Then I outlined (selected) all three cells, grabbed the little dark square at the bottom right hand corner of that selection with the mouse, and then dragged it down. It filled in an increasing series of A#'s in the cells below.

I have Excel 2003, so that should probably work in whatever version you have.

This should definitely work. Excel will tell you what it’s going to try and put in the cells when you drag the mouse down, so you’ll know if it’s working or not.

Yeah that would work. In fact you don’t even have to do that. Sometimes it is annoying that Excel automatically increments the cell contents. Like you put A1 in the first cell, double click the drag down and it increments it even though you just wanted to copy A1 all the way down.

You can go to Excel Options->Formulas and click “Manual Calculations” to disable that “feature.”

That’s interesting. I’ll check tomorrow. By coincidence I did that today because I was updating a spreadsheet with heaps of workbooks using vlookups and got sick of it recalculating every time I did anything.

Yeah, I’ve gotten used to doing that when I run my 250,000-row, 50-column workbook with 7 vlookup tables and approximately 7 million calculations. My computer has Roberto Duran’d on it a few times now.

These methods all work, but here’s another one just for the heck of it:

If you wanted to do A1 thru whatever starting from the first row, you could use:

=“A”&ROW(A1)

when you drag that formula down, the number portion will be equal to the row that the cell is in

If you wanted to start the formula in a row other than row 1, but still have the numbering start at 1, you could just insert subtraction into the equation. For example, if you wanted to start in row 10, you would use this formula:

=“A”&ROW(A10)-9

Formulas like this can be handy for doing lots of things, the row and column functions are useful and sometimes overlooked

My technique was really a cludge. One of the others is probably better.