Creating Dynamic ranges formulae [SOLVED]

    I have an excel question. I am creating a spreadsheet that adds data every day. The data is added as a new row at the top. The data is added by column. I have several formulas at the end of each column. They are the sum of the last 5,10,15 and 25 days for each column. What I want to do is add the row and keep the formula's cells(content) the same, but when I add the row, the value of the cells in the formula change to match the new placement of the cells. Can anyone suggest anything?
    If this isn't clear just let me know and I will try to explain further. Thanks in advance. :saint:

    I think offset formulae will be the best to use, and better way is to created named ranges with offset formulae!!!


    I will try to do something more, but it will be best if you can give data!!!

    Thanks: ~Yogendra

    I have created a sample data for you,
    see if this helps.


    Created 3 Dynamic Ranges
    Days_5:
    =OFFSET(Sheet1!$A$1,0,0,MATCH(TODAY()-4,Only_Dates,FALSE),6)
    What it does???
    -This formula stats from the first cell
    -matches today's date - 4 (to get 5 days including today!!)
    -Selects a range starting from first cell, rows = the figure of match we derived above and cols = 6


    Same thing applies for Days_10 and Days_15


    Now in the formulae::
    Sum of column say "A" for last 5 days::
    =DSUM(Days_5,B$25,Full_Data)
    What it does???
    This formuma takes sum of Data_5 we generated above for column "A" (B$25 = "A" which is in summary) with criteria of full_data.


    I have done a quick QA.


    Let me know if you want anything more.
    Also see the Sheet attached.


    :cheers::cheers::cheers:

    Thanks: ~Yogendra

    Hi tinka1


    Can you please change the original subject to something like :
    Creating formulae Dynamic ranges


    (Click on Edit in your main post)


    This will help others to use this when required and even you may find some better ways of doing this than suggested mine.


    I hope you won't mind this.
    :cheers:


    P.S. : It will be great if you can put : SOLVED in the main text when you are fully satisfied.

    Thanks: ~Yogendra

    Hi Chris,


    Good to see you motivating people for their nice work.


    I felt really good, you were free to use it any way, but it was good of you to ask.


    :thumbcoo:

    Thanks: ~Yogendra

    Hi tinka1,


    I had the very same problem that I solved with the following:
    I use E2 (header row) with new rows being added in E3. I use this for "Average", but it works for sum as well.


    =SUM(OFFSET(E2,1,0):E112) "sum of all days" E112 is dynamic and will change to E113 etc as rows are added.


    =SUM(OFFSET(E2,1,0):OFFSET(E2,5,0)) "sum of 5 days"



    =SUM(OFFSET(E2,1,0):OFFSET(E2,10,0)) "sum of 10 days"



    =SUM(OFFSET(E2,1,0):OFFSET(E2,15,0)) "sum of 15 days"



    =SUM(OFFSET(E2,1,0):OFFSET(E2,25,0)) "sum of 25 days"


    Hope this helps, 2rrs

    I want to thank everyone for there wonderful
    help. All the tips were great. Sorry for the
    delay in answering.
    This Topic can be closed but it will never
    be forgotten.
    :coolwink:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login