Control chart from hidden data sheet

Hi Experts,

I would like to request Experts help. Need to control Line Chart from Chart 2 sheet based on “Spinner 1”. Currently I’m controlling Chart display at Chart sheet with “Spinner 104”. The reason is, I’m intent to hide Chart sheet from user. Hope Experts will help me to add this feature at “Chart 2”. Attached the workbook for Experts perusal.



Chart-1.xls
CartilloAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
How's this? I skipped the code and just linked the spinner to a cell.
Chart-1.xls
CartilloAuthor Commented:
Hi Rorya,

Is that possible to activate "Sub InsertIssue()" together. The reason is the data that was displayed at Cell B98 and onwards (Chart sheet) need to display at Chart 2 sheet.
Rory ArchibaldCommented:
Where do you want on Chart2?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

CartilloAuthor Commented:
Hi Rorya,

Yes, you're right.
Rory ArchibaldCommented:
Sorry, I meant to say where do you want it on chart 2? Presumably not in the same rows?
CartilloAuthor Commented:
Hi Rorya,

Please target at row B42 onwards.
Rory ArchibaldCommented:
Does this do what you need?
Chart-1-1-.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CartilloAuthor Commented:
Hi rorya,

Cool! how you did this? Please guide.
Rory ArchibaldCommented:
I rewrote your InsertIssue macro to use the Chart2 sheet for output rather than Chart, and then linked the spinner directly to cell S2 on the Chart sheet and set its min and max values to 1 and 13 respectively (as you have 13 columns of data). All of those are set by right-clicking and choosing Format Control.
HTH
Rory
CartilloAuthor Commented:
Hi Rory,

Thanks for the guide. Just realized cell E39 at Chart 2 is no logger updating data  based on spinner's selection, please help.
CartilloAuthor Commented:
Hi Rory,

Similar situation for Issues, the data was not updated. Please assist
Rory ArchibaldCommented:
Change the formula in E39 to:
=INDEX(Chart!D2:P54,ROUNDDOWN((TODAY()-Chart!C2+6)/7,0),MATCH(Chart!R1,Chart!D1:P1,0))

There is no Issues sheet, so I don't know what that refers to.
CartilloAuthor Commented:
Hi Rorya,

Now the cell shows as "#N/A"

The "Sub InsertIssue()" is actually update automatically data from "Daily Tracking List" sheet (hidden) from column J "Issues" while we select spinner.
Rory ArchibaldCommented:
I'm not seeing #N/A - see attached.
Chart-1-1-.xls
CartilloAuthor Commented:
Hi,

Thanks a lot for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.