define range anchor points within a formula by cell values

  • Can anyone help me to update the formulae in a graph-data-range so that I can change the range definitions according to values in other cells.


    for example one cell in the graph data range contains the formula
    =SUMIF($B$497:$B$861,B7,$D$497:$D$861)
    ...........(big-compare, little compare, sumif)


    column B contains dates from b6 to b950
    column D contains values from d6 to d950
    so I am reporting on a sub section of all dates.


    I can alter the value of little-compare by changing the value in cell b7
    but I have failed when trying to set the anchor points of the big-compare range in the same way


    I have tried experimenting with macros, but I can't help thinking that I am missinga simple way to do this


    I attach a cut down example


    thanks for any help.
    Mark

  • Re: define range anchor points within a formula by cell values


    Hi Mark,


    Does this help.

    Code
    D22: =$B$39:$B$94 
    
    
    D6: =SUMIF(INDIRECT($D$22),B6,$D$39:$D$94)


    This will use the text description of the range $B$39:$B$94
    You will still need to define the range in D22 somehow but I'm not sure whether this is what you are after.
    Post back if you need more help.

  • Re: define range anchor points within a formula by cell values


    Superb!!!


    With a bit of tweeking I put in a middle step so that all I need to do is enter the start and end row values into two cells, and the whole graph is updated. Then by liberal use of range names the formulae become virtually understandable:-)


    to wit:
    step1 - name two cells StartCurrent & EndCurrent



    step2
    *** calculate bigCompare range and display by string formula
    ...="$B"&+StartCurrrent&+":$B"&+EndCurrent to give ... $B467:$B832


    *** calculate SumifRanges for each column and display by string formula
    ... ="$D"&+StartCurrrent&+":$D"&+EndCurrent to give ... $D467:$D832
    ... ="$E"&+StartCurrrent&+":$E"&+EndCurrent to give ... $E467:$E832


    (I'm sure I can now get rid of absolutes too)



    Step3
    I now have the 'text' decsription I need for the INDIRECT function which you showed me


    and I end up with nice graph-data formulae such as
    =COUNTIF(INDIRECT(BigCompare),B843)
    =SUMIF(INDIRECT(BigCompare),B845,INDIRECT(SumifColD))
    =SUMIF(INDIRECT(BigCompare),B845,INDIRECT(SumifColE))



    This technique would have bee soooo useful in the past, thanks for the tuition.




    Quote

    Post back if you need more help.


    A rash offer!


    Actually yes,there is one more refinement I would like.
    I would like to be able to type in the relevant start & end dates and then for the StartCurrent & EndCurrent cells cells to look up the row number in the source data table.
    any ideas?


    I attach my revised version in case you are intrigued to see what I've done with your help.


    regards

  • Re: define range anchor points within a formula by cell values


    Thank you Andy


    The =match formula has done the trick.


    I can now just type in 2 dates and find the distribution of sales by weekday between them.


    This is exactly what I was aiming for when I started, so Thanks Again.



    regards
    Mark

Participate now!

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