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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top