Results 1 to 5 of 5
  1. #1
    planner
    Guest

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

    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

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 Problems -1A bug(?) 2 A Help please

    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
    Attached Files Attached Files

  3. #3
    planner
    Guest

    Re: 2 Problems -1A bug(?) 2 A Help please

    [img]/w3timages/icons/doh.gif[/img] 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

  4. #4
    planner
    Guest

    Re: 2 Problems -1A bug(?) 2 A Help please

    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.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 Problems -1A bug(?) 2 A Help please

    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? [img]/w3timages/icons/mad.gif[/img]

    Jon

Posting Permissions

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