Create a "Total" field for an auto populating list

%3CLINGO-SUB%20id%3D%22lingo-sub-2245501%22%20slang%3D%22en-US%22%3ECreate%20a%20%22Total%22%20field%20for%20an%20auto%20populating%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2245501%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20an%20excel%20form%20that%20pulls%20a%20list%20of%20entries%20from%20an%20Object%20in%20Salesforce%20and%20automatically%20adds%26nbsp%3Bthe%20value%20of%20each%20item%20in%20the%20list%20to%20create%20a%20total%20field.%20%26nbsp%3BProblem%20is%2C%20the%20number%20of%20items%20being%20pulled%20changes%20and%20using%20a%20%22%3Dsum(a1%3Aa3)%22%20syntax%20stops%20adding%20after%20row%203%20no%20matter%20how%20many%20new%20lines%20have%20been%20added.%20%26nbsp%3BIs%20there%20a%20way%20to%20change%20the%20syntax%20in%20the%20Total%20field%20to%20always%20include%20the%20number%20of%20rows%20added%20in%20the%20list.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-03-30%20at%202.33.47%20PM.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F268371i4B3EC765517CD772%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-03-30%20at%202.33.47%20PM.png%22%20alt%3D%22Screen%20Shot%202021-03-30%20at%202.33.47%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2245501%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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.