Monday, October 13, 2014

yogi_Consolidate The Cost From Various Purcahase Sheet Into The ConsolidatedHistory Sheet

                 Google Spreadsheet   Post  #1792
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-13-2014
post by  Justin Byrd:
(https://productforums.google.com/forum/#!mydiscussions/docs/EPas6jd6438)
Total below array formula.
Hello,

I am using this formula to consolidate the form responses of several PURCHASE forms:

=ArrayFormula( SORT (vmerge( 'PurchaseHistory(MaceyF)'!B7:J , 'PurchaseHistory(RichardM)'!B7:J , 'PurchaseHistory(JustinB)'!B7:J , 'PurchaseHistory(KeithB)'!B7:J , 'PurchaseHistory(BlakeM)'!B7:J ) ; 9 ; True))

I would like to have a running total of the cost of each entry at the bottom of the array formula. But I receive a #REF! error  "Array result was not expanded becuase it would overwite data in E8." 

How can I go about accomplishing this?
Thanks,
Justin B.
---
Hello Yogi,

Thanks for the reply!


Hope that link works, its the first time doing this.

I would like a "Total" bar directly below the list on the sheet "PurchaseHistoryConsolidation," that calculates total cost. The totals bar would match the formatting in PurchaseHistory(justinB) and would be placed on B29 assuming the current number of form submissions.

Thanks, Justin B.
---------------------------------------------------------------------------------------------------------------------------------
based on my suggestion of having the Totals on the top rather than at the bottom, I presentthe following  solution