• 2 Problems -1A bug(?) 2 A Help please

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » 2 Problems -1A bug(?) 2 A Help please

    Author
    Topic
    planner
    Guest
    January 17, 2001 at 2:27 am #1767774

    I have two questions (greedy)
    1. I have linked two spreadsheets, the master contains a calculated dates formatted to MMM. The second spreadsheet has a graph that then uses the dates as the x axis. If I open the two spreadsheets everything is OK. When I close the master, the second sheet then only displays the dates in numeric format ie 36892. I have tried formating the axis, referencing the data indirectly but all to no avail.

    Now the How do I…
    2. How do I create a sliding range in a graph. E.G. This month I want to reference Jan 00 – Jan 01 but next month obviously I want Feb 00 – Feb 01. The master data sheet gets updated with the next available column. Again this is a linked spreadsheet as above.

    Sorry for the headache but Thanks

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSJon Holbrook
      AskWoody Lounger
      January 20, 2001 at 2:00 am #1776417

      I guess if you have two questions, you’ll be really greedy and want two answers. Well, I’ll try.

      1) I reproduced the problem, but it went away when I formatted the axis by right clicking on it, selecting Format Axis, then Number and finding the correct format. I assume that’s what you did when you say you formatted the axis. Exactly what version of excel are you using? (go to Help>About Microsoft Excel and the full version is up the top. I’m using 97 SR-2(g)).

      2) Question 2 is a little easier. The trick here is to use range names that are defined with a formula. For example, if you create a range name with the following formula:
      =offset(sheet1$a$2,3,0,12)
      then you will get back the 12 values that start 3 rows down from a2. You can then use these range names in your graph.
      I’ve attached an example where the starting month is set via a number you put in a cell in the workbook. If you have a look at that and still think “what the cracking heck is going on there”, then let me know and I’ll go through the steps.

      Jon

      Reply | Quote
      • planner
        Guest
        January 20, 2001 at 6:06 pm #1776459

        Jon. Many thanks for the answer to question 2. I have been using spreadsheets for a few years now and always thought I knew them quiet well, but I have never used formula’s directly in a range. That one’s worth knowing. That gives me good scope for solving that problem.

        As for what release of Excel I am using, I will get back to you as it is the system at work. I have tried formatting the axis both as custom and date types.

        Many Thanks

        Reply | Quote
      • planner
        Guest
        January 21, 2001 at 6:18 pm #1776490

        Jon
        In reply to problem 1. I have checked our version of Excel and it’s 97 sr1. I would not be able to get this changed as it is a company release. I have attached the slimmed down version of the linked spreadsheet. You will need to move the data on sheet two to a new book and use that as the linked workbook. I would be intrigued if this is an error on yours. You will need to adjust the links to suit your file structure.

        Reply | Quote
        • WSJon Holbrook
          AskWoody Lounger
          January 22, 2001 at 6:53 am #1776556

          The same problem occurred on my setup. I think the problem is related to the fact that you have one x-axis label marked Current, so you are mixing dates and strings. As soon as I changed Current to =Now(), the problem went away. Looks like a bug?

          Jon

          Reply | Quote
    Viewing 0 reply threads
    Reply To: 2 Problems -1A bug(?) 2 A Help please

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.