Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Problem with a table

    I have Excel 2007. So my Charts would update automatically when new data is entered, I formatted the data as a Table. I have not used tables before, but I think I created it correctly. It has header rows, and no total row. I add new data by clicking on the row immediately below the last row with data, and the row is selected correctly as part of the table.

    I have 5 different charts created on separate pages. There are 10 different columns which contain data that is plotted on the 5 charts. Three of the charts always update perfectly. However, the other two do not ever update. I cannot determine why this is happening. The two columns that create the charts that do not update generally have data entered every other row, but not always. The other columns that create the charts that do update generally have data entered every row, but sometimes data is missing, so there is no entry.

    Any idea why this might be happening, or what I might investigate?

    Lex

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you post the workbook with any confidential information removed/altered?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Can you post the workbook with any confidential information removed/altered?
    Rory:

    I tried to upload the file several times. I get an error message that says it is an invalid file. It is an .xlsb file. Will this forum not accept that type? It is 818 KB.

    Or let me know your Email address and I will send it to you directly. I have taken out all sensitive text.


    Lex

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lex,

    Zip the file or same as .xlsm and you shouldn't have any problem uploading.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Can you post the workbook with any confidential information removed/altered?
    Rory:


    I have attached it as a .zip file. The columns with the headers 'if not home' and '& Comments' had text entries, which I removed. I also removed text entries from the charts.

    The charts (and columns) which I have problems with are Weight and Temp. All the other ones update correctly. I manually updated the chart SERIES for Weight and Temp yesterday, so that they all now reflect data through rows 5911.

    Feel free to made any additions, changes, etc. This file has a different name than my original, so you cannot damage anything.

    Thanks,

    Lex
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your weight and temp charts don't use the entire table columns as their source, so you don't get the benefits of the dynamic ranges.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Your weight and temp charts don't use the entire table columns as their source, so you don't get the benefits of the dynamic ranges.
    Rory:

    Is that because I started the Chart SERIES range for Weight at line 2278 and Temp at line 2371, rather than at line 13, where all the rest start? Or is there some other reason?

    Harry

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Lex Vs. Harry,

    Until you get things straightened out, you can use this code as a workaround. Place in the Data sheet's module. When the date is entered in the next row in column A, the ranges for all the graphs are automatically resized to a range including the new row

    HTH,
    Maud

    Data sheet module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    StartCol = Array("C", "F", "M", "G", "H")
    EndCol = Array("D", "F", "N", "G", "H")
    StartRow = Array(13, 13, 13, 2731, 2731)
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        For I = 0 To 4
            Sheets(I + 1).Activate
            ActiveChart.SetSourceData Source:=Sheets("Data").Range("A" & StartRow(I) & _
                ":A" & LastRow & ", " & StartCol(I) & StartRow(I) & ":" & EndCol(I) & LastRow)
        Next I
    End If
    Worksheets("Data").Activate
    Application.ScreenUpdating = True
    End Sub
    Last edited by Maudibe; 2014-10-18 at 04:29.

  9. #9
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Maud:

    Thanks for the code. But I must confess I have no idea what to do with it! I went to the Visual Basic area, and added it under the Modules section of PTS_ChangeSeriesFormula, and then saved it. See the attachment.

    But it did not work. Also, I could not figure out how to change the name from Module 1.

    So this novice needs some more help to implement the code that you so kindly wrote for me.


    Lex
    Attached Images Attached Images

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Lex,

    The code needs to be placed in the module belonging to Data Sheet. You have placed it in a standard module.

    Maud

    Code1.png

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by hmw View Post
    Rory:

    Is that because I started the Chart SERIES range for Weight at line 2278 and Temp at line 2371, rather than at line 13, where all the rest start? Or is there some other reason?
    Yes, exactly that.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Lex,

    The code needs to be placed in the module belonging to Data Sheet. You have placed it in a standard module.
    Maud:

    Thanks. Don't know how I got it in the wrong place to begin with--although as I said, I have never done VBA before.

    I did notice that each time I start a new row, Excel seems to be calculating for 5 seconds or so before I can enter any data. Why is this?

    Harry

  13. #13
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Yes, exactly that.
    Rory:

    Started them at row 13 along with the other columns, and it all works fine now. I then changed the chart starting dates for Weight and Temp, so they do not reflect the dates of earlier rows.

    Thanks so much for your help.

    Harry

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excel seems to be calculating for 5 seconds or so before I can enter any data. Why is this?
    Probably because you have 5000 rows for each on the 5 graphs and that I most likely did not use the most efficient method in my code.

    After playing with this, I found another option you have to keep all your data without have to trim the front rows. Adjust the chart data range for Temp and weight so that the range is one more and the range for the other charts. Here is an example using you posted workbook in post#5. After I made that change, adding a line to the table updated all the graphs.

    HTH,
    Maud

    ChartDataRange2.png

    Temp
    Attachment 40041
    Attached Images Attached Images
    Last edited by Maudibe; 2014-10-20 at 18:42.

  15. #15
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Maud:

    I can't get that to work. The Weight and Temp SERIES do not seem to increment by adding a row each time I select the next row. I added as many as 6 rows to Temp & Weight beyond where data has been entered, but they never incremented forward. In my testing I was always entering data at least two rows past what I set in Weight & Temp. i.e., if I started with data through 5911, I set Weight/Temp to 5917, and I added data through at least 5919. Weight/Temp never incremented past 5917.

    I had first disabled the VBA code which I assume you did also.

    All the remainder of the charts updated as they should.

    Harry

Page 1 of 2 12 LastLast

Posting Permissions

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