 # 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

## Files

• 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.

[h4]Cheers
Andy
[/h4]

• 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

## Files

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

Hi,

I have added data validation to the start and end date cells.
The to the cells that return the row position I added a MATCH() formula.

## Files

[h4]Cheers
Andy
[/h4]

• 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!