Forum Discussion

hughhamill's avatar
hughhamill
Copper Contributor
Apr 04, 2024

Two linked tables in an excel (on sharepoint) but the 2nd is simply deleting rows

So I have two tables. One table takes the raw data from a Microsoft Form and updates as the form is filled in and the 2nd table in another tab takes averages of those answers. The 2nd table rows simply say "=average(a1:a4)" - the averages are working fine. What is not working fine is that whenever, for example, Row 5 is created in Table 1, that should update Row 5 in Table 2 but what Table 2 in the 2nd tab is doing at that point is simply deleting the row that should update from Row 5 in the first table.

 

So Row 5 now says equals row 6 of the first table. 

 

Does it make sense??

 

Bascically, instead of adding in the row information from the first table, it is deleting the row the information should go to in the second table??

excel
Formulas and Functions
training

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 06, 2024

    hughhamill 

    It sounds like there might be a misunderstanding regarding how Excel tables and formulas work, particularly in the context of linked tables on separate sheets. Here's what could be happening and how you can address it:

    1. Table Range Reference:
      • Ensure that the formulas in the second table are correctly referencing the corresponding cells in the first table. For example, if the first table starts in cell A1 on the first sheet, and you want the second table to reference the same range on the second sheet, the formula in the second table should be =AVERAGE('Sheet1'!A1:A4).
    2. Auto-expanding Tables:
      • If the first table is an Excel table (created using the "Format as Table" feature), it will automatically expand as new data is added. However, formulas referencing the table's range will not automatically update to include new rows unless the entire column is referenced (e.g., =AVERAGE('Sheet1'!A:A)). If you want the formulas to adjust automatically, you'll need to ensure that the entire column is referenced.
    3. Structured Table References:
      • If you're using structured table references (e.g., Table1[Column1]) in your formulas, Excel should automatically adjust the references when new rows are added to the table. However, ensure that the formulas are correctly referencing the table and column names.
    4. Data Refresh:
      • If the tables are linked to external data sources, ensure that the data is being refreshed properly. Excel may delete rows if it cannot refresh the data due to errors or other issues.
    5. Check for Filters or Sorting:
      • Make sure that there are no filters applied to the second table that could be hiding rows or interfering with the data display. Similarly, sorting of the table could cause confusion in the row references.

    By addressing these potential issues, you should be able to ensure that the second table updates correctly as new data is added to the first table, without deleting rows unexpectedly. If you're still encountering issues, double-check the formulas and data structure to identify any discrepancies or errors. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources