Having Letters change within a formula

[vdogeek]

Hi Guys And Gals. I seem to be stuck with a spreadsheet needed to track monthly usage for materials we use here.. This sheet pulls information from another sheet which gets filled daily by someone else.. the sheet I am making will not allow Letters to change when copy/pasting from 1 cell to another. I'll do my best to give you examples...below is the 4 weeks of 1 part being pulled to "say" the month of March

=SUM(SUM(HARDWARE!C8:C12),SUM(HARDWARE!C15:C19),SUM(HARDWARE!C22:C26),SUM(HARDWARE!C29:C33),SUM(HARDWARE!C36:C40))/20

When i copy this to the cell below it it shows this

=SUM(SUM(HARDWARE!C9:C13),SUM(HARDWARE!C16:C20),SUM(HARDWARE!C23:C27),SUM(HARDWARE!C30:C34),SUM(HARDWARE!C37:C41))/20

I need the Letters to change but the "numbers" not to change,
like the line below

=SUM(SUM(HARDWARE!D8:D12),SUM(HARDWARE!D15:D19),SUM(HARDWARE!D22:D26),SUM(HARDWARE!D29:D33),SUM(HARDWARE!D36:D40))/20

I need the letters to go from a-bc

I hope this makes sense...
Thank you!

 

[Jonmo1]

Obersvation and a question first..
You don't need to repeat the SUM function for each range, you can supply multiple ranges to the original sum function.
So you can do
=SUM(HARDWARE!C8:C12,HARDWARE!C15:C19,HARDWARE!C22:C26,HARDWARE!C29:C33,HARDWARE!C36:C40)/20

What is in the 'between' cells (C13:C14, C20:C21, etc)
If they are not numeric, you can just do SUM(C8:C40)
If they are numeric, then is there anything in another column that can be used as a key to indicate which rows should be included or ignored ?


What you want can be done, but it will involve some Offset and Row functions, and must be repeated for all 5 ranges.
So I'm trying to find a way to reduce your formula to a single range reference.

 

[vdogeek]

Thank you for this....I will change the original line to what you specified.

VDO

 

[Jonmo1]

To do it the hard way, and make the columns incriment when you drag the formula down
You have to do this to each range..

Change your range reference,
HARDWARE!C8:C12
to
OFFSET(HARDWARE!$C$8:$C$12,0,ROWS($A$1:A1)-1)

Do that to each of the 5 ranges in your formula.

 

[vdogeek]

I've tried adding this to the formula,, it keeps the numbers from changing but does not change the letter from "C" to "D", is the formula intended to do this for me, or have possible not entered it correctly in the formula?

=SUM,Offset(HARDWARE!$C$99:$C$103,0,ROWS($A$1:A1)-1)Offset(HARDWARE!$C&106:$C&110,0,Rows($A$1:A1)-1)OFFSET(HARDWARE!$C$113:&c$117,0,ROWS($A$1:A1)-1)OFFSET(HARDWARE!$C$120:$C$124,0,ROWS($A$1:A1)-1)OFFSET(HARDWARE!$C&127:&C$131,0,ROWS($A$1:A1)-1))/20

 

[Jonmo1]

You need commas between each offset function.
=SUM(OFFSET(..),OFFSET(..),OFFSET(..))

And you won't 'See' the column letters change. The offset function makes it 'Refer' to the appropriate column though.

 

[vdogeek]

Thank you...but i would really want to see the letters change as i "drag down" and copy...It is quit a large spread sheet and I hate having to change all letters manually

 

[wideboydixon]

Then you have to use VBA; Excel will change the row if you drag formulas down and the column if you drag them across. You can't drag formulas down and change the column. With the cursor in the first cell that contains your formula you could run this macro:

Public Sub CopyFormulaDown()

ActiveCell.Copy ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Formula = ActiveCell.Offset(0, 1).Formula
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(1, 0).Activate

End Sub

That would do what you want I think.

WBD

 

[vdogeek]

I'll give this a try....Thank you!