drag-across of INDIRECT function does not change cell reference between " " marks

Hi all,

I'm having an issue with dragging cell formulas across and having them update the column reference.

I have multiple formula referencing a single cell (cell A2) to get a numeric value to incorporate into a cell reference - so I can update this single cell and have all the formula calculate over increasing ranges as I add data to my spreadsheet each month. I have used the INDIRECT function over several formula in the same column, but when I drag across to the right, the column letter reference does not update because it is in " " marks.

I did find a similar solution on the forum, but am confused since my " " marks contain two column/letter references.

The formula I am trying to drag across are like this:

=ConsecUpDays(INDIRECT("D25:D" &$A$2))
=MaxPosSequence(INDIRECT("D25:D" &$A$2))

=AVERAGE(IF((INDIRECT("D25:D" &$A$2))>0,((INDIRECT("D25:D" &$A$2))),""))

=COUNTIF((INDIRECT("D25:D" &$A$2)),"<>0")

Any help would be much appreciated!

Example ..

use AVERAGEIF function ...

=AVERAGEIF(INDIRECT(ADDRESS(25,COLUMN(D:D))&":"&ADDRESS($A$2,COLUMN(D:D))),">0",INDIRECT(ADDRESS(25,COLUMN(D:D))&":"&ADDRESS($A$2,COLUMN(D:D))))

Thanks kev_,

I did experiment with that formula, but could not get it to work with my formula and have Excel accept it. I'm wanting to change the "D25:D" values to "E25:D", "G25:G" as I drag the formula across.

Not sure how to get inside the " " marks to have it increment by column letter as I drag.

Very big Thank You _kev and John Topley.

I see what you meant now _kev. Thanks John, I was able to use that to amend my formula and make them draggable.

My formula now read:

=ConsecUpDays(INDIRECT(ADDRESS(25,COLUMN(D:D))&":"&ADDRESS($A$2,COLUMN(D:D))))
=MaxPosSequence(INDIRECT(ADDRESS(25,COLUMN(D:D))&":"&ADDRESS($A$2,COLUMN(D:D))))

=AVERAGEIF(INDIRECT(ADDRESS(25,COLUMN(D:D))&":"&ADDRESS($A$2,COLUMN(D:D))),">0",INDIRECT(ADDRESS(25,COLUMN(D:D))&":"&ADDRESS($A$2,COLUMN(D:D))))

=COUNTIF((INDIRECT(ADDRESS(25,COLUMN(D:D))&":"&ADDRESS($A$2,COLUMN(D:D)))),"<>0")

Much appreciated.