Copy down formula - change colulm not row

Hello there!

I want it so that when I copy down a formula it with cell references in it, it changes the colulm not the row

e.g.

A4
B4
C4
D4

Rather than

A4
A5
A6
A7

Thanks very much!!

Alex

=ADDRESS(4,ROW())

If it's referencing the incorrect column, add a + or - value after the 'row()' text.
e.g.
=ADDRESS(4,ROW()+2)
=ADDRESS(4,ROW()-3)

Thanks for that though it doesn't seem to do quite what I'm after - here is the whole formula:

=AVERAGEIF('Company Footprint'!E5:'Company Footprint'!E245,1,Country_Risk!$E$4:$E$245)


I want it to copy down as follows:

=AVERAGEIF('Company Footprint'!F5:'Company Footprint'!F245,1,Country_Risk!$E$4:$E$245)

=AVERAGEIF('Company Footprint'!G5:'Company Footprint'!G245,1,Country_Risk!$E$4:$E$245)

=AVERAGEIF('Company Footprint'!H5:'Company Footprint'!H245,1,Country_Risk!$E$4:$E$245)

=AVERAGEIF('Company Footprint'!I5:'Company Footprint'!I245,1,Country_Risk!$E$4:$E$245)

THANK YOU!

Alex

In that case...

=AVERAGEIF(INDIRECT("'Company Footprint'!"&ADDRESS(5,ROW())&":'Company Footprint'!"&ADDRESS(245,ROW())),1,Country_Risk!$E$4:$E$245)

Again, bear in mind this formula uses the row number to get the required column, so if you put this formula in row 6 it'd be the equivalent of F5/245, row 9 would be I5/245, etc.

If you needed row 2 to look in column F, you would need to adjust the row() references accordingly - i.e row()+4.

Similarly, if any rows are subsequently added in above the data, you would need to adjust the row() value accordingly.