Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Excel 2003 question - propagating cells

Six pack on a dickRegistered User regular
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?

matt has a problem on

Posts

• Registered User regular
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

• Registered User regular
Sounds like a Pivot Table task.

• Registered User regular
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

• Six pack on a dick Registered User regular
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.

• Registered User regular
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).

• Six pack on a dick Registered User regular
I want to propagate down a column, the top being (formula)!B32, the bottom being (formula)!AE32

• Registered User regular
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.

• Six pack on a dick Registered User regular
Yeah, tried it, doing that just makes it increase the number while keeping the same two or three letters over and over.

• Registered User regular
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.

| Playing - TSW & MWO| Backlog Wars - Lost! | Steam!
Viewing the forums through rose colored glasses... or Suriko's Ye Old Style and The PostCount/TimeStamp Restoral Device (Updated 8.10.2012)
• Registered User regular
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.

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

• Six pack on a dick Registered User regular
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!