Dragging my formula to the right that goes straight from $C$8:$C$12 to $C$14:$C$18 and so on, without breaking the whole formula

[rogerfoster88]

A huge thanks to Fluff and Jack for helping me get to where i am with my formula.

=SUMIF('[Production Plan.xlsx]Production Plan draft'!$C$8:$C$12,B138,'[Production Plan.xlsx]Production Plan draft'!$D$8:$D$12)/6+SUMIF('[Production Plan.xlsx]Production Plan draft'!$J$8:$J$12,B138,'[Production Plan.xlsx]Production Plan draft'!$K$8:$K$12)/6

This IS WORKING however I can't click and drag to the right and it work for other cells...

Is there a way to have $C$8:$C$12 and $D$8:$D$12 change to $C$14:$C$18 and $D$14:$D$18, and have $J$8:$J$12 and $K$8:$K$12 to change to $J$14:$J$18 and $K$14:$K$18 and have them all continue on that pattern for as long as i drag to the right? Taking the $'s out before the numbers only has the formula change from $C8 to $C9 as you would expect.

Jack mentioned possibly adding to the formula something that tests the row number via IF and applies the above formula if TRUE, but after playing with it over the last 20 hours or so i am having no luck.

Any Help will be greatly appreciated.

 

[tyija1995]

Hey, would you be dragging this formula across 6 columns to go from $C$8:$C$12 (etc) to $C$14:$C$18 ?

 

[rogerfoster88]

Hi Tyija,

Just one column

 

[deadlyduck]

You could consider just copying + pasting the existing formula to the first new cell (C14) then do a find and replace of $C$8:$C$12 to $C$14:$C$18 in that new cell. Repeat for the other cells.
[h=2][/h]

 

[tyija1995]

I notice that the formula contains only information from this "Production Plan draft" sheet - are you writing the formula on another tab? I have a suggestion that I can write up if you use the Production Plan draft tab to write the formula (so you don't have to reference the worksheet) you can then later link a cell from another tab to this formula - basically I have found a way to augment the columns as you have requested, but I need to be writing the formula in the same tab as the data

 

[tyija1995]

Assuming you are writing the formula on the sheet that is being calculated on (Production Plan draft) then try this:

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

Every time you copy this across 1 column it will go from C8:C12 to C14:C18 - it steps 6 rows if you will.

You can use your other worksheet/workbook to link to this formula if it helps. I assumed B138 would be changing to C138 etc as it gets copied across columns.

This initial formula before copying across reads the same as:

=SUMIF(C8:C12,B138,D8:D12)/6+SUMIF(J8:J12,B138,K8:K12)/6

Just the augmenting of the formula across columns has been updated to suit.

 

[rogerfoster88]

I could run it along side the production plan then have the main sheet read the result of that cell.

In regards to the B138 to C138, B138 will stay B138. Will this effect the outcome of the formula you mention below?

Assuming you are writing the formula on the sheet that is being calculated on (Production Plan draft) then try this:

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

Every time you copy this across 1 column it will go from C8:C12 to C14:C18 - it steps 6 rows if you will.

You can use your other worksheet/workbook to link to this formula if it helps. I assumed B138 would be changing to C138 etc as it gets copied across columns.

This initial formula before copying across reads the same as:

=SUMIF(C8:C12,B138,D8:D12)/6+SUMIF(J8:J12,B138,K8:K12)/6

Just the augmenting of the formula across columns has been updated to suit.

 

[tyija1995]

I could run it along side the production plan then have the main sheet read the result of that cell.

In regards to the B138 to C138, B138 will stay B138. Will this effect the outcome of the formula you mention below?

Yes that first sentence is what I would do, OK so you just need to lock in the B138 reference, $B138 will suffice as you are copying across columns only and not rows. So this would be the full formula to keep B138 when copying across columns:

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),$B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),$B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

Put this wherever suits best on the Production Plan draft sheet, copy it across and you'll have what you have asked (C8:C12 --> C14:C18 --> C20:C24 etc etc) for each column spanned. Let me know if it looks right!

 

[rogerfoster88]

Yes that first sentence is what I would do, OK so you just need to lock in the B138 reference, $B138 will suffice as you are copying across columns only and not rows. So this would be the full formula to keep B138 when copying across columns:

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),$B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),$B138,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

Put this wherever suits best on the Production Plan draft sheet, copy it across and you'll have what you have asked (C8:C12 --> C14:C18 --> C20:C24 etc etc) for each column spanned. Let me know if it looks right!

Really appreciate the help!

So I've moved the cell it looks at, from B138 on the original spreadsheet to AS28 in the production plan draft sheet and its returning "0" if the data in C8:C12 and/or J8:J12 matches AS28 rather than the sum it should be in D8:D12 and K8:K12.

Adjustment shown below -

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),$AS8,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),$AS8,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

 

[tyija1995]

Really appreciate the help!

So I've moved the cell it looks at, from B138 on the original spreadsheet to AS28 in the production plan draft sheet and its returning "0" if the data in C8:C12 and/or J8:J12 matches AS28 rather than the sum it should be in D8:D12 and K8:K12.

Adjustment shown below -

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),$AS8,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),$AS8,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

The adjusted formula you wrote references $AS8 instead of $AS28 - is this a typo? Given that $AS28 is on the Production Plan draft sheet the formula should still work.