Autofill complexity

Hello,

I have this formula in a cell (K27 but irrelevant)
=($E$70*C4)+($E$71*C5)+($E$72*C6)+($E$73*C7)+($E$74*C8)+($E$75*C9)+($E$76*C10)

E70:E76 is a set of prices that will stay constant.

C4:C10 is a set of quantities.

I want to drag down and autofill from K27. E70:E27 will stay constant because of $ which is good.

Here is the tricky part. in K28 I want the formula to have replaced C4:C10 with E4:E10. so autofill should take from every other column. Is there any way to do this, there has to be a way to do it without doing it manually

Thanks

copy formula down to K28.
Do a find and replace. on K28. Find C REPLACE with E.
select both K27 and K28. drag and drop down

I want to drag down about 30 cells. K28 will need to have E like you have stated. but then K29 will have to be G. K30 will use I... So I will be using every other column in the reference.

To be more clear,
I currently have in K27
=($E$70*C$4)+($E$71*C$5)+($E$72*C$6)+($E$73*C$7)+($E$74*C$8)+($E$75*C$9)+($E$76*C$10)

in K28 I want
=($E$70*E$4)+($E$71*E$5)+($E$72*E$6)+($E$73*E$7)+($E$74*E$8)+($E$75*E$9)+($E$76*E$10)

in K29 I want
=($E$70*G$4)+($E$71*G$5)+($E$72*G$6)+($E$73*G$7)+($E$74*G$8)+($E$75*G$9)+($E$76*G$10)

K27:

Formula:

Please Login or Register  to view this content.

and drag across

Awesome thanks! is there a way I can do it with dragging down, rather than dragging across . but i can do it if i drag across and have them away from everything in the spreadsheet then copy and paste transpose i guess

Can't work out how to drag it down, however ...

Let's say you put the formula in cell L27:

Formula:

Please Login or Register  to view this content.

and dragged across.

Now, select cells K27:K32. With all six (6) cells selected, type:

Formula:

Please Login or Register  to view this content.

and commit with Ctrl-Shift-Enter rather than just Enter (as it is an Array Formula)