Create a "Total" field for an auto populating list

New Contributor

I'm trying to create an excel form that pulls a list of entries from an Object in Salesforce and automatically adds the value of each item in the list to create a total field.  Problem is, the number of items being pulled changes and using a "=sum(a1:a3)" syntax stops adding after row 3 no matter how many new lines have been added.  Is there a way to change the syntax in the Total field to always include the number of rows added in the list.

Screen Shot 2021-03-30 at 2.33.47 PM.png

3 Replies

@denverlawyer 

One option is:

  • Remove the current total row.
  • Convert the data range (including the header row) to a table.
  • Tick the check box 'Table Row' for the table (on the Design tab of the ribbon, under Table Tools). 

S0258.png

@Hans Vogelaar 

 

Thanks Hans.  I tried your solution and for some reason it won't let me use the Define Names function to insert the fields from Salesforce.  It seems like as soon as I try and use that function to insert the appropriate field label, Excel deletes it the cell reverts back to blank.  Is there a trick to inserting fields into the cell of a Table?

@denverlawyer Try another method then:

Insert an empty row between the data and the total row.

Include the empty row in the SUM formulas, but always insert new rows above that empty row.