Simple reference

[hsandeep]

C2=K45
So C3 should be K46 & C4=K47.
Formula needed in C3 & C4.
Thanks in advance.

 

[hsandeep]

C3 is 1 row down from C2, So C3=K(45 + 1) i.e. K46

 

[ukmikeb]

Hi

Drag the formula down perhaps.

 

[hsandeep]

Hi

Drag the formula down perhaps.

I keep on changing C2 'manually'. If I make C2=T77, then in C3 it should automatically 'consider' T78 i.e. T(77 + 1) since C3 is 1 row down from C2. Moreover since column C has not changed, so T should not change here either.
Ex:
C2=T77 (input)
D3=U78 (answer)
Any questions, pls ask.

 

[ukmikeb]

Hi

Try in C3 :-

=CHAR(CODE(LEFT($C2,1))+COLUMNS($C:C)-1)&RIGHT($C2,LEN($C2)-1)+1

which can be dragged down and across but is limited to a single character A to Z.

Other than that what are you trying to achieve?

hth

 

[hsandeep]

Try in C3 :-

=CHAR(CODE(LEFT($C2,1))+COLUMNS($C:C)-1)&RIGHT($C2,LEN($C2)-1)+1

which can be dragged down and across but is limited to a single character A to Z.

It showed #VALUE!
In C2=K4
I copied the above formula in C3 which should have given me value of K5 but it is giving #VALUE!

 

[ukmikeb]

Hi

A graphic representation :-
hsandeep[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[/TR]
[TR]
[TH]2
[/TH]
[TD]k4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3
[/TH]
[TD]k5
[/TD]
[TD]l5
[/TD]
[TD]m5
[/TD]
[/TR]
[TR]
[TH]4
[/TH]
[TD]k6
[/TD]
[TD]l6
[/TD]
[TD]m6
[/TD]
[/TR]
[TR]
[TH]5
[/TH]
[TD]k7
[/TD]
[TD]l7
[/TD]
[TD]m7
[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

Cell	Formula
C3	=CHAR(CODE(LEFT($C2,1))+COLUMNS($C:C)-1)&RIGHT($C2,LEN($C2)-1)+1
D3	=CHAR(CODE(LEFT($C2,1))+COLUMNS($C:D)-1)&RIGHT($C2,LEN($C2)-1)+1
E3	=CHAR(CODE(LEFT($C2,1))+COLUMNS($C:E)-1)&RIGHT($C2,LEN($C2)-1)+1
C4	=CHAR(CODE(LEFT($C3,1))+COLUMNS($C:C)-1)&RIGHT($C3,LEN($C3)-1)+1
D4	=CHAR(CODE(LEFT($C3,1))+COLUMNS($C:D)-1)&RIGHT($C3,LEN($C3)-1)+1
E4	=CHAR(CODE(LEFT($C3,1))+COLUMNS($C:E)-1)&RIGHT($C3,LEN($C3)-1)+1
C5	=CHAR(CODE(LEFT($C4,1))+COLUMNS($C:C)-1)&RIGHT($C4,LEN($C4)-1)+1
D5	=CHAR(CODE(LEFT($C4,1))+COLUMNS($C:D)-1)&RIGHT($C4,LEN($C4)-1)+1
E5	=CHAR(CODE(LEFT($C4,1))+COLUMNS($C:E)-1)&RIGHT($C4,LEN($C4)-1)+1

<tbody>
[TD="bgcolor: #FFFFFF"]
Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]


The following formula entered in C3 :-

=OFFSET(INDIRECT($C$2),ROWS($C$3:$C3)-1,COLUMNS($C:C)-1)

and drag down

having entered "K45" in C2 will give you the contents of cells K45, K46 and K47 in C3, C4 and C5.

If you then drag across it will give you the contents of cells L45, L46 and L47 in D3, D4 and D5.

Please note however that it's not possible to enter "=K45" in C2 and get the expected results because the formula will have no reference to base itself on.

hth

 

[hsandeep]

The following formula entered in C3 :-

=OFFSET(INDIRECT($C$2),ROWS($C$3:$C3)-1,COLUMNS($C:C)-1)

This works. Thanks. Just...Will this Offset formula slow down my Excel workbook compared to simple "=K34" kind of formulas being used at 40 cell address?

 

[ukmikeb]

HI

Thanks for the feedback.

Yes, it will slow the workbook only if you have a lot of "heavy" formulas in this or other sheets.

If you don't have those sorts of formulae you're unlikely to notice the difference.

What you lose in performance you have to weigh it against the gains in flexibility through not having to drag the formula after each entry.

hth