Cell reference and graph problem

Stingrad

New Member
Joined
Jul 4, 2002
Messages
30
I have 2 issues firstly I have a cell A1 that returns the current date this is fine. I have a list of dates from the start to the end of the year and I need to return the cell reference that the equivlent date is in. (dates in cells A2 to A366.

I then need to create a chart using a changing cell the problem I have with this is that as soon as I select a cell as the source data it uses that cell and not the reference in it. I.E B1=Sheet1!$D$6:$I$17 but when I select this as source data the graph just shows B1 and not the reference in it.

Hope this is clear.

All help appreciated
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
What you're trying to do is the equivilant of sticking and "INDIRECT()" inside the SERIES() formula, which Excel doesn't like so much.

Ideas off the top of my head: you could try setting the source to a named range and then write a small macro to set the range referred to by said named range. There may be a way to apply a Named Formula to do what you want as well. I'd search here for keywords Chart, Offset and maybe Series and see if that finds a thread that discusses this concept. Also, you might want to check the indexed list of articles.

Or if you're really ambitious, you can look at John Walkenbach's site and use his Class module for Chart Series and see if you can apply that.

Sorry for not providing a more direct solution, but it's late on Friday and this was all I had time for.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top