Copying formula choices?

[krisryan]

Good Morning,

I am trying to copy my formula down a column but excel is copying down instead of over.

My first cell formula in the column is: =sum('OCT20:SEPT25'!B6:B44) I want to go down that column with this formula (it is a long column) so it should be

=SUM('OCT20:SEPT25'!B6:B44)
=SUM('OCT20:SEPT25'!C6:C44)
=SUM('OCT20:SEPT25'!D6:D44)

and so on with the last one being:

=SUM('OCT20:SEPT25'!BU6:BU44)

When I copy and paste the formula it goes from B6:B44 to B7:B45. How can I force it to copy they way I want it? I am hoping there is an answer that doesn't require me setting each formula manually.

Thanks in advance.

[Limor_OP]

I think you need to fix the column with "$"
=SUM('OCT20:SEPT25'!$B6:$B44)

Maybe like that? It's hard to advise without seeing the file, or a sample of it...

[krisryan]

That doesn't seem to have worked. I have attached the Workbook. The tab I am trying to put the formulas in is "SeaFighterOH"

[krisryan]

In column P

[wk9128]

TRY

=SUM(OFFSET(INDIRECT(*****worksheets name and ranges************),,ROW(A1)-1))

[krisryan]

I appreciate the suggestion but that doesn't work either.

[Limor_OP]

It's not clear enough what you're trying to do ?

[wk9128]

It was offline last night. At that time, your attachment was not uploaded. After reading your attachment, I found that your area cannot contain B44. If there is already an addition, it will be calculated.

'OCT20:SEPT25' Put all the statistical worksheets in an area like this

example : ONLY THREE OCT20,NOV20 and DEC20
=SUMPRODUCT(N(INDIRECT({"DEC20","NOV20","OCT20"}&"!"&CHAR(65+ROW(A1))&ROW($6:$43))))
OR
SUM(SUMIF(OFFSET(INDIRECT({"DEC20","NOV20","OCT20"}&"!B6:B43"),,ROW(A1)-1),"<>"))

[krisryan]

I attached the workbook.

[wk9128]

Please refer to attachment P6 cell


Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select “Solved” from the Thread Tools menu