Excel 2003 question - propagating cells

matt has a problem

This should be simple, but I can't figure it out, nor can I describe it to Google well enough for an answer.

I want to place a formula in a cell, for example =[DailySheet20092010.xls]Nov2009!L32 and drag it to propagate the other cells in the column with ascending letters, so it reads L32, M32, N32 and on.

When I paste that formula into said column and drag, it propagates the cells in my new column as L32, L33, L34. How do I get it to ascend by letter, and not number?

Daenris

drag horizontally.

I'm not immediately aware of any way to switch the default behavior. If you drag vertically, it will adjust the row number, if you drag horizontally it will adjust the column letter.

If you really need to have it in one column, you can create the formulas by dragging horizontally, then copy it and Paste Special, Transpose

Arikado

Sounds like a Pivot Table task.

Orogogus

What do you want it to do once it goes past Z?

Without worrying about that, CHAR() will change numeric codes (A-Z = 65-90, a-z = 97-122) to the corresponding character. So you can do something like =CONCATENATE(CHAR(COLUMN(L32)+64), ROW(L32)).

Getting it to go AA, AB, AC, etc. would be more complicated. I think this is how I would do it:

1. Divide the column # by 26
2. Use FLOOR() on that
3. If it's zero, then you'll concatenate "" to the front later
4. Otherwise use CHAR() to spit out the correct first letter
5. Take the remainder from step 1, use CHAR() and produce the second letter
6. Use ROW() as above
7. Concatenate the 3 components back together

matt has a problem

Daenris wrote: »

drag horizontally.

I'm not immediately aware of any way to switch the default behavior. If you drag vertically, it will adjust the row number, if you drag horizontally it will adjust the column letter.

If you really need to have it in one column, you can create the formulas by dragging horizontally, then copy it and Paste Special, Transpose

I tried this, it gives me a #REF error though, removing the column and number.

I need it to go up to AE also, due to 31 days in the month.

Orogogus

Just to be clear: Do you want to propagate across a row, or down a column? Your example had the target cell and the desired output as L32, so I assumed that you wanted the letters to go up as your went right, down the row (matching the column letters).

matt has a problem

I want to propagate down a column, the top being (formula)!B32, the bottom being (formula)!AE32

RUNN1NGMAN

Have you tried making the first cell your formula!B32, second cell same formula except formula!C32, and then highlighting both and dragging? Sometimes you have to give Excel a hint about the pattern you want it to follow.

matt has a problem

Yeah, tried it, doing that just makes it increase the number while keeping the same two or three letters over and over.

Jobastion

Try this.
Start with (for example) =B$2, and go ahead and copy/paste that across all the columns you want represented. That will get you =B$2 thru =AE$2

Then copy all of them, highlight the area you actually want the formula's to go into for real, and use
Daenris's suggestion of Paste Special, Transpose.
That will keep the letters and numbers all the same. Just tested it myself, so give it a whirl.

Orogogus

Ah. I had thought you wanted the actual output to be B32, C32, etc., but you're talking about the formula bar. Completely misunderstood. Sorry.

INDIRECT() lets you enter a cell reference (e.g., D3 or R1C4) and get the value from the corresponding cell. You'll want to set the optional second argument to FALSE so that it's using the R1C1 format (since the ROW() and COLUMN() functions only provide numeric values, I think).

I would basically use the ROW() function combined with INDIRECT() to switch the row and column around.

So instead of a function like

=1+B32

you have

=1+INDIRECT(CONCATENATE("R32C", ROW(A2)), FALSE)
(when in cell A1. A2 can be any cell from row 2, and the reason it's A2 instead of A1 is because your example starts at B32; if it started at D32 then you could use A4)

===

EDIT: But it really would be easier to go row->row column->column first, and then Paste Special -> Transpose as others suggested.

RUNN1NGMAN

Could you just transpose the reference info so that its in a column rather than a row?

matt has a problem

Jobastion wrote: »

Try this.
Start with (for example) =B$2, and go ahead and copy/paste that across all the columns you want represented. That will get you =B$2 thru =AE$2

Then copy all of them, highlight the area you actually want the formula's to go into for real, and use
Daenris's suggestion of Paste Special, Transpose.
That will keep the letters and numbers all the same. Just tested it myself, so give it a whirl.

Awesome, this worked. I hadn't though of using the $ before dragging and copying. You've just saved me from having to hand-enter 1200 entries, thanks!