Very easy question! (But not sure what to call it - maybe transpos

[Alexander]

I have the following table:
A B C
1 Name Value Number
2 Alex R 1.00 20
3 Andrew R 2.00 30
4 John R 3.00 40
5 Paul R 4.00 50
5 Shaun R 5.00 60

I don't want to do a direct transpose, but want to know how to get a formula
to increase automatically.

I.e. I want to enter the formula for Alex:Value =B2. I want to drag the
Value formula across all the names (see below), and I want the number to
increase by one, while keeping the column reference fixed. How do I do that?

I.e.
Alex Andrew John Paul Shaun
Value =$B2 =$B3 =$B4 =$B5 =$B6
Number =$C2 =$C3

If I drag $B2 across it just repeats $B2.

How do I do this?

Many Thanks

Alex

 

[Bob Phillips]

=INDIRECT(ADDRESS(ROW(),COLUMN(B:B)))

 

[Alexander]

Hi Bob

Is there no shortcut key like pressing alt or ctrl or something, so that
when you drag a reference it increases the number without the $ sign? I.e. if
you drag down it automatically increases the number without the $ sign, and
if you drag across it automatically increases the colm ref, i.e. D to E.

If dragging across, how do I get it to increase the number? I can keep the
column constant using a $ sign. I.e. $D2, the next one after dragging should
be $D3. Can this be done?

Regards

Alex

 

[Teethless mama]

for Value:
=INDIRECT("B"&COLUMN(B:B))
copy across

for Number:
=INDIRECT("C"&COLUMN(B:B))
copy across