Help with copying forluma to next colum

[akrams]

Need your help please. I have a sheet with 2 tabs. I have an average function in the second tab as below.

=AVERAGE('Ad Hoc'!HO5:HU5)+AVERAGE('Ad Hoc'!HO6:HU6)+AVERAGE('Ad Hoc'!HO7:HU7)

When I drag the same formula to the next Colum, I want the cell to count 7 columns after HO5:HU5 (Which should be HV5:IB5) but when I drag the formula across it gives me HP5:HV5. Is there anyway to fix it, so it counts 7 columns after the last Colum HU5?

Thanks for all your help.

 

[PaddyD]

in which cell is your current formula?

 

[Michael M]

Do a Find and Replace on your formula.
Replace the = with a #.
Copy and paste to the new location, then do another find and replace on BOTH ranges,
but this time Replace # with =

Regards
Michael M

 

[Stormseed]

Do a Find and Replace on your formula.
Replace the = with a #.
Copy and paste to the new location, then do another find and replace on BOTH ranges,
but this time Replace # with =

Regards
Michael M

hi michael,

for quite some time now I was hitting around the bush to implement INDIRECT() nested with COLUMN() for getting this question resolved, nevertheless, i cud not find the answer and still trying.

It would be nice if you could explain the logic of the solution you posted because when I tried your solution on my computer it did not work out !

How can the reference of 7 more columns be added onto the next column when the formula is copied onto the next column by using just Find/Replace ??

 

[DonkeyOte]

You could use OFFSET to build a dynamic range... essentially this creates a range 1 row high and 7 columns wide but adjusts starting position of HO5/6/7 by 7 columns each time the formula is moved across to a new column in summary sheet.

The below assumes formula is going in Column A of your sheet

=AVERAGE(OFFSET('AD HOC'!$HO$5,0,(COLUMN(A1)-1)*7,1,7))+AVERAGE(OFFSET('AD HOC'!$HO$6,0,(COLUMN(A1)-1)*7,1,7))+AVERAGE(OFFSET('AD HOC'!$HO$7,0,(COLUMN(A1)-1)*7,1,7))

If the first formula on summary sheet is not in Column A you must adjust:

(COLUMN(A1)-1)*7

and set the -1 to be the number of the starting column of the formula (if D set to -4, Z set to -26 etc...)

I hope that helps.

 

[Michael M]

Hi Stormseed
Unless I misunderstood the question, changing the + to # truns the formula to text.
When copied across it keeps the same cell reference.
Replacing # with = puts it back to a formula.
Given the number of posts you and I have done, I would assume that I was the one that had it wrong.

Regards
Michael M

 

[Stormseed]

Hi Stormseed
Unless I misunderstood the question, changing the + to # truns the formula to text.
When copied across it keeps the same cell reference.
Replacing # with = puts it back to a formula.
Given the number of posts you and I have done, I would assume that I was the one that had it wrong.

Regards
Michael M

No fret, my friend. You always learn from your mistakes.

 

[DonkeyOte]

Regardless, I think the question was how to write a formula which could be "dragged" across, Edit/Replace method doesn't drag as far as I'm aware

 

[akrams]

Thanks a million to all for your replies. lasw10, I tried your tip which worked perfectly.

Thanks again.