Extending formulas perpendicularly

[trijamms]

Hi,

I been running searches for this with no luck so hopefully somebody can help. I'm trying to extend a formula down but across columns. Let me give you the example.

I am in cell BL2 and want it to equal cell B10. No problem, I type "=B10". Now I want to extend the formula, but instead of the cell number to increase, I want the cell letter to increase, because my data is across columns. Therefore, when I extend the formula, I want the next one to be "=C10", not "=B11".

Like so:

=B10
=C10
=D10
...

Is there a way I can do this? I appreciate any help. Thanks!

Hubert

 

[jeffreybrown]

Hi Hubert,

Try...

=INDIRECT(CHAR(ROW(A66)) & 10)

...and then drag down

 

[Caribeiro77]

Welcome to the board..
But do you want to copy your formula(=B10) down( across rows) or right/left( across colmuns)?

 

[trijamms]

Welcome to the board..
But do you want to copy your formula(=B10) down( across rows) or right/left( across colmuns)?

I'd like to copy down for data that is stored across.

 

[trijamms]

Thanks Jeff. It worked! Can you explain how though. I'm gonna need to use this on a regular basis now. Thanks!

 

[jeffreybrown]

The inner part of the formula simply returns the row number --->> ROW(A66))

Next --->> CHAR(ROW(A66)) returns the character for a 66 --- >> B

Now add it altogether with =INDIRECT(CHAR(ROW(A66)) & 10) and you get a named cell reference --- >> Indirect("B10")

Place your cursor on the cell with the formula, then...

Formulas >> Formula Auditing >> Evaluate Formula

Now select the Evaluate button and you can step thru the building of the formula

 

[trijamms]

The inner part of the formula simply returns the row number --->> ROW(A66))

Next --->> CHAR(ROW(A66)) returns the character for a 66 --- >> B

Now add it altogether with =INDIRECT(CHAR(ROW(A66)) & 10) and you get a named cell reference --- >> Indirect("B10")

Place your cursor on the cell with the formula, then...

Formulas >> Formula Auditing >> Evaluate Formula

Now select the Evaluate button and you can step thru the building of the formula

OK. I just realized that it only works up to a point. Once it reaches char(91), it stops following the Excel alphabet (X, Y, Z, AA, AB...), so beyond that it doesn't work. Any suggestions?

Hubert

 

[Jonmo1]

Try

=INDEX($10:$10,ROWS($1:1)+1)

 

[njimack]

=indirect(address(10,row(b2)))

 

[trijamms]

Thanks guys. The last two suggestions from Jonmo and njimack both work flawlessly. I figured out how the INDIRECT one works. I'll have to look more into the INDEX method though, since I am unfamiliar with it. I really appreciate everyone that took the time to help me out. Hopefully, in some future version of Excel, Microsoft will make this process a bit easier!

Hubert