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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,751
Office Version
  1. 2016
  2. 2013
  3. 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
19,751
Office Version
  1. 2016
  2. 2013
  3. 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,712
Messages
5,597,720
Members
414,168
Latest member
Manapo

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