Reverse cell referencing/relativity

[agni452]

In sheet 1, I have a list of things in col A, rows 1-20. In sheet 2, I have a formula in cell A1 that says basically "IF('Sheet 1'!$A1="val",5,0)". However, on sheet 2 I have reversed the references so that B1 would ask "IF('Sheet 1'!$A2="val",5,0)", and C1 would ask "IF('Sheet 1'!$A3="val",5,0)", etc. However, when I click-drag that formula from A1 to T1, I need it to increment the row number, not the column letter. Basically, I need it to behave as if I'm dragging the formula from A1 to A20, except horizontally from A1 to T1. Is there any way to do that?

[Jakobshavn]

If in Sheet2 cell A1 you enter:

=INDIRECT("Sheet1!A" & COLUMN()) and copy across

You will have the same effect as entering:

=Sheet1!A1 in A1
=Sheet1!A2 in B1
=Sheet1!A3 in C1
etc.

As you copy across, the reference goes downwards.

Is this of any help??