Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

How Dropbox changes chart values...

Posted on 2014-04-20
Medium Priority
580 Views
The attached is a solution from Saqib at:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28415626.html#a40011010

This works wonderfully but I do not understand how value 10 enters into cell Formulas!L2  when September is selected from the dropbox at cell Sheet1!D2.

Basically, how this dropbox changes the value of all months to zero except the selected month (which is set equal to 10).

If there was an event associated with the drop box, I could loop through and do all these; but there is no event to work with.

Question: How this dropbox manipulates Formulas!D2:AA:2?

Also, when I pick Jan 2014, instead of setting cell Formulas!P2 to 10, it sets cell Formulas!D2 to 10. After I learn how the dropbox works, I may be able to handle this part also.

Thank you,
SampleChart.xlsm
0
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 15

Assisted Solution

unknown_routine earned 400 total points
ID: 40011340
There is no VBA Code , all logic in insde the drop down.

Change Excel mode to design mode, then right click on the drop down,

here you see:

Input Range: Lookup!\$L\$3:\$L\$26

Cells n the Formula sheet have formula for example L2:

=IF(COLUMN()-3=MONTH(CurrMth),10,0)
0

LVL 34

Author Comment

ID: 40011346
re:> Change Excel mode to design mode

How do I do this? Now using Excel 2010.

Thanks,

Mike
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 1600 total points
ID: 40011598
Change formulas!D2 to

=IF(D5=CurrMth,10,0)

and copy it across.

If the current month is equal to d5 it will show 10 otherwise it will show 0.
0

LVL 34

Author Closing Comment

ID: 40011838
Thank you.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses
Course of the Month11 days, 13 hours left to enroll