Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart Wizard (Excel 97, SR2)

    I have created a new workbook containing about 800 rows of purchase order data by copying selected information from a previously existing workbook. In the new workbook, I have computed some average purchase prices based on quarter ending dates. When I select the 8 quarter ending dates and the corresponding average prices the chart wizard gives me a message that "your formula contains and invalid external reference to a worksheet." I have checked to make sure that the new workbook is not linked to any other workbook. The existing workbook and the new workbook have different names as do their respective worksheets. Can any one explain why I am getting this message or how I can get around it?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Wizard (Excel 97, SR2)

    Could you get rid of Proprietary info, delete the unused cols and rows and attach the workbook so we could look at it?

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart Wizard (Excel 97, SR2)

    Steve, attached is a new workbook I created by taking the quarterly information and "special pasting - values only. If you select the 8 quartely dates and the 8 prices in $ per lb. and attempt to run the chart wizard, you get the same error message that I get on the workbook with all the data. I hope this helps.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Wizard (Excel 97, SR2)

    What exactly are you selecting?

    In XL97, When I select the range: B3:B17 then hold ctrl and select F3:F17 and run the chart wizard it works fine

    If I select (holding ctrl) the individual cells B3, B5, B7, ...B17, F3, F5, ..., F17 and run the chart wizard it works fine.

    So I can't reproduce the problem.

    Steve

  5. #5
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart Wizard (Excel 97, SR2)

    Steve, I select B3, then F3, then B5, then F5 etc. until all eight dates and average values have been selected. When I run the chart wizard on those selections the preliminary window opens, I choose the line chart and click next, that opens the "preview" which looks fine, but when I click next again I get the error message.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Wizard (Excel 97, SR2)

    <P ID="edit" class=small>(Edited by sdckapr on 30-Jan-04 12:27. Added another comment on shortening sheetname)</P>AHA. I thought you got the error before the wizard ran, didn't go far enough.

    The problem is, by selecting the cells individually, you are creating a reference to multiple cells. The "text" that this entails gets Long:
    =SHEETHISTORY!$B$3,SHEETHISTORY!$B$5,SHEETHISTORY! $B$7,SHEETHISTORY!$B$9,SHEETHISTORY!$B$11,... etc
    When it gets transfered it comes upon the 256 char formula limit so you do not have all the references.

    The solution is to:
    Select the range: B3:B17 then hold ctrl and select F3:F17 each as a contiguous range and excel will use the range:
    =SHEETHISTORY!$B$3:$B$17,SHEETHISTORY!$F$3:$F$17

    which won't be truncated due to length and not give you the problem.

    I think it would be much easier making these 2 selections than the 16 individual ones anyway.

    Steve
    PS.Another solution (if you must select them individually) is to shorten the sheetname to "A" and it will not be too long (though you might run into problems later)

  7. #7
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart Wizard (Excel 97, SR2)

    Steve, Thank you very much. I was selecting each fields I wanted to chart individually because they were separated by all the rows that were summarized. Now that I understand the problem, I'll move the summary data to fields that I can select in the method you suggest. Thanks again

Posting Permissions

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