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

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