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