SOLVED

Totals in Table Get Errors When Inserting, Deleting or Sorting Rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2207892%22%20slang%3D%22en-US%22%3ETotals%20in%20Table%20Get%20Errors%20When%20Inserting%2C%20Deleting%20or%20Sorting%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207892%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20a%20maintenance%20log%20and%20it%20has%20columns%20for%20parts%20cost%2C%20labor%20cost%2C%20total%20cost%20and%20cumulative%20cost.%3C%2FP%3E%3CP%3EIt%20appears%20that%20the%20total%20cost%20column%20formula%20gets%20confused%20when%20I%20insert%20a%20row%20somewhere%20in%20the%20table%20or%20delete%20a%20row%20and%20the%20cell%20row%20references%20change.%26nbsp%3B%20Sorting%20also%20seems%20to%20sometimes%20generate%20these%20errors.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20worksheet%20file%20and%20hope%20someone%20can%20help%20me%20with%20the%20total%20cost%20formula%2Ffunction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EGeorge%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2207892%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2208215%22%20slang%3D%22en-US%22%3ERe%3A%20Totals%20in%20Table%20Get%20Errors%20When%20Inserting%2C%20Deleting%20or%20Sorting%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2208215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F990681%22%20target%3D%22_blank%22%3E%40George_Weston%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20because%20you%20are%20not%20using%20structural%20reference%20in%20the%20formula%20in%20Total%20Cost%20Table%20Column.%3C%2FP%3E%3CP%3EReplace%20the%20existing%20formula%20in%20L14%20with%20the%20following%20one%20and%20then%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%5B%40%5BCost%20of%20Parts%5D%5D%2B%5B%40%5BCost%20of%20Labor%5D%5D%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2208904%22%20slang%3D%22en-US%22%3ERe%3A%20Totals%20in%20Table%20Get%20Errors%20When%20Inserting%2C%20Deleting%20or%20Sorting%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2208904%22%20slang%3D%22en-US%22%3EThank%20you.%20Works%20well.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2209450%22%20slang%3D%22en-US%22%3ERe%3A%20Totals%20in%20Table%20Get%20Errors%20When%20Inserting%2C%20Deleting%20or%20Sorting%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209450%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F990681%22%20target%3D%22_blank%22%3E%40George_Weston%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi

I have a maintenance log and it has columns for parts cost, labor cost, total cost and cumulative cost.

It appears that the total cost column formula gets confused when I insert a row somewhere in the table or delete a row and the cell row references change.  Sorting also seems to sometimes generate these errors.  

 

I have attached the worksheet file and hope someone can help me with the total cost formula/function.

 

Thanks!

George

5 Replies
best response confirmed by George_Weston (Occasional Contributor)
Solution

@George_Weston 

That's because you are not using structural reference in the formula in Total Cost Table Column.

Replace the existing formula in L14 with the following one and then copy it down.

 

=[@[Cost of Parts]]+[@[Cost of Labor]]

You're welcome @George_Weston! Glad it worked as desired.

I need to learn about structural references. I was aware of them but not really familiar with using them. So, thanks again. I'll be doing some learning.
Regards,
George
Happy learning!
All the best!