Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Macro announcement

    An irritating warning pops up when I use a drop down selection box. It pops up on only one sheet of 3 that have the same information and formulas, differing only on numbers and rows. Will attempt to display.Macro warning.JPG .

    I don't have any macros in that or any of my sheets - at least where I can find them. Everything works - it is just that I have to remove the warning every time before proceeding. So there must be something somewhere. Drop down box works fine on the other sheets.

    Thank you

    Larry

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by larrynz View Post
    An irritating warning pops up when I use a drop down selection box. It pops up on only one sheet of 3 that have the same information and formulas, differing only on numbers and rows. Will attempt to display.Macro warning.JPG .

    I don't have any macros in that or any of my sheets - at least where I can find them. Everything works - it is just that I have to remove the warning every time before proceeding. So there must be something somewhere. Drop down box works fine on the other sheets.

    Thank you

    Larry
    It looks like your drop-down list has had a macro associated with it at some point in time. If you right-click on the control and select Assign Macro from the context menu that appears, is there a macro name in the dialog box presented to you? Select it with the mouse and press Delete on the keyboard to get rid of it. Then click OK to come out of the dialogs and try again.

  4. The Following User Says Thank You to jeremybarker For This Useful Post:

    larrynz (2014-04-07)

  5. #3
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I swear that I tried that before, however I did it again -- and it worked! So thank you for solving this irritation.


    Since I seem to have a someone with more knowledge than I about excel, perhaps you can solve my real problem.

    Have a list of stocks that I track weekly and chart. My problem is that as time moves on the list of entries gets longer. After 3 years the list of price entries is 156. This becomes burdensome and I would like to start anew with perhaps another 3 year period, but maintain the graph showing the past action(rise or fall). I have not been able to come up with a solution that does not destroy the stock graph's continuity.
    Probably not making myself clear, but perhaps you can understand the basic problem.

    Thanks again for solving my 1st problem.

  6. #4
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That is interesting. Not sure I understand the formula. Should one be sheet 2, instead of both using sheet1? And if I understand the thought, this would make the new sheet/series/section or whatever, be connected to the older section. But honestly I never hear or thought of appending. This doesn't look too difficult - I might be able to utilize it with my limited knowledge. Will let you know if it works.
    In any case thanks so much. Is it warming up in PA?

  7. #5
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Since I am rather new using this lounge, I might have done or not done something, but I can't locate the information you sent concerning the Append function example you wrote. I don't see it in the above post. Did I delete it, or is it lurking somewhere? Sorry, I hate to keep bothering you, but could you send it again with perhaps an explanation of what it is suppose to do? It takes me a bit of time to figure out what the function might be doing and many times my assumptions are incorrect.
    Again thank you for you wisdom and time

  8. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    larry,

    You can append your ranges for your series if your columns become too long. It can be from different columns on the same sheet or a different sheet. The graph will be continuous. Edit your series and separate your ranges by a comma

    =(Sheet1!$B$2:$B$16,Sheet1!$E$2:$E$16)

    HTH,
    Maud

    appendSeries.png

  9. #7
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud, Thanks. That is exactly what I wanted (I think, maybe I misled you and need something else) I have some problems concerning use of the formula. Stupidly I go to the newsheet and am not certain what cell to place the formula into, or does it not matter? And when I return to Excel2 sheet, I find apostrophes placed onto Excel2. is this correct? and what do they mean or why are they there? I believe I copied the formula correctly.
    =Excel2!$B$6:$J$156,newsheet!$B$6:$J$156
    ='Excel2 '!

    Actually I am working with the info below, which also contains VLookup, Index, dropboxes and other functions. The party who helped me set this up has passed away and my source of info is gone. While I understand parts and am getting good at copying & pasting, there is much I don't understand. I considered it easy to use Append, but am not certain it is what I need. And another thought - the data for the charts must always be corrected to adjust for the longer string of numbers. Which also makes me wonder if/how to number items on sheet Newsheet? Must I continue weekly entry numbers (say a 3 year period to 156) or could I start another 3 year period from 1 to 156 Instead of i57 to 312?

    Maud, if this is becoming more that you want to continue with, feel free to tell me. I feel I am becoming a pest. I appreciate your help but perhaps I really don't know what I want. Which was simply to keep track of stocks & chart them.

    Below is an example to give you better insight.
    Cell #VALUE is the cell I placed you append formula.

    Current 41698 115.78 0.94 11.02 3.19 1.13 7.92 44.42 11.44
    Highest 41698 118 1.09 12.83 3.19 1.16 8.17 44.42 12.17
    Average 41652.5 114.885 0.923571429 11.29285714 3.086428571 0.999285714 7.898571429
    Shares 100 600 527.679 1998.114 600 1374.09 648.264 524.873 317.417
    #VALUE! BRK.B CBEH CH CIM EMXX GIM O PNNT SSW
    Nov 29, 2013 116.53 0.85 10.85 2.95 0.84 8.17 38.11 12.17
    Dec 6, 2013 116.66 1 10.66 3.01 0.85 8.06 37.08 11.93
    Dec 13, 2013 114.06 0.83 10.38 3.04 0.88 7.82 38.25 11.74
    Dec 20, 2013 115.6 0.7 11.08 3.19 0.873 7.87 37.64 11.58
    Dec 27, 2013 118 0.75 10.98 3.08 0.917 8.06 37.86 11.57
    Jan 3, 2014 117.57 0.83 10.59 3.14 1.11 7.95 37.73 11.56
    Jan 10, 2014 114.97 0.82 11.25 3.03 1.03 8.11 38.56 11.28
    Jan 17, 2014 115.07 1.09 11.88 3.03 1.02 7.94 38.41 11.33
    Jan 24, 2014 111.81 1.06 12.83 3.11 1.03 7.84 39.54 11.24
    Jan 31, 2014 111.6 1.08 11.62 3.12 1.01 7.6 40.78 11.32
    Feb 7, 2014 112.61 1.07 11.79 3.12 1.05 7.73 40.56 11.25
    Feb 14, 2014 114.95 0.99 11.73 3.11 1.16 7.75 41.72 11.36
    Feb 21, 2014 113.18 0.92 11.44 3.09 1.09 7.76 43.41 11.6
    Feb 28, 2014 115.78 0.94 11.02 3.19 1.13 7.92 44.42 11.44

    Larry , My Email address is: larrynz1@gmail.com

  10. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    Larry,

    Not a problem. The formula does not go into a cell. It goes into the Edit Series dialogue box. Right Click on your graph and select "Select Data...". Highlight your series then press enter Edit. In the Edit Series dialogue box, enter the ranges for your columns separated by a comma

    I was mistaken when I commented that the ranges can be on different sheets for the same series. I have used data from ranges on different sheets before but they must have been for values in totally separate series in the same graph. Can you post a sample of your sheet with an explanation? The adjustments that you mention can be automatically done through code.

    Maud

  11. #9
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,206
    Thanks
    46
    Thanked 231 Times in 212 Posts
    Larry,

    I emailed you a revised edition of your spreadsheets with the corrected formulas, resolved issue of cells with zeroes, functional dropdown box, and corrected averages. Hope it is what you are looking for.

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •