Problem with a table

Topic

New Reply

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

Reply | Quote

Replies

Can you post the workbook with any confidential information removed/altered?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Lex,

Zip the file or same as .xlsm and you shouldn’t have any problem uploading. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Lex,

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

Maud

38191-Code1

Reply | Quote

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

Reply | Quote

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

38200-ChartDataRange2

Temp

Reply | Quote

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

Reply | Quote

hmw,

The vital signs I entered went from normal to hypertensive, tachycardic, febrile, and obese with the next set swinging in the other direction of hypotensive, bradycardic, hypothermic, and anemic. All the charts just followed along. The attached workbook has no code in it at all. I started by entering the new date in Column A. Very strange if it does not do the same for you.

Reply | Quote

Maud:

This is VERY strange. I used the _Revised.zip spreadsheet you sent me. The Weight and Temp SERIES showed 1 more row than the others when I opened it.

I tried adding 1 row, 2 rows, etc. And I started like you did, clicking on Column A to start the next row. Weight and Temp would not increment at all.

Wonder if there is some Excel option setting that is different between your setup and mine? I didn’t see anything that might address this issue, but I am no expert.

Harry

Reply | Quote