Help with copying forluma to next colum

akrams

New Member
Joined
Oct 25, 2005
Messages
19
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,275
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

Banned
Joined
Sep 18, 2006
Messages
3,274
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 ??
 
Last edited:

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,275
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

Banned
Joined
Sep 18, 2006
Messages
3,274

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Oct 25, 2005
Messages
19
Thanks a million to all for your replies. lasw10, I tried your tip which worked perfectly.

Thanks again.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,871
Messages
5,834,150
Members
430,261
Latest member
quangtinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top