Hi Guys
I have a three tables:
Customers
AppQuotesItems
AppQuotesParts
Both AppQuotesParts and AppQuotesItems have columns which calculate totals, VAT, Discount etc, they are both exactly the same in structure other than the table name.
I have a report which initially is based on Customers and pulls data from AppQuotesItems
<<Start: FILTER(โAppquotesitemsโ, AND([JobID]=[_THISROW].[JobID], [Status]=โAuthorisedโ))>><<[Item]>>โฆand several other column data follows โฆ this all works very wellโฆ
I can do the same for AppQuotesParts very easily:
<<Start: FILTER(โAppquotesPartsโ, AND([JobID]=[_THISROW].[JobID], [Status]=โAuthorisedโ))>><<[Item]>>โฆetc
And here is wherethe problem lies.
How do i Sum the values of both on the same report ??
You can SUM in Word
Thanks Alex
So how would I SUM both values?
The report does SUM data from AppQuotesItems already (example):
<< SUM(SELECT(Appquotesitems[Cost] , AND([JobID]=[_THISROW].[JobID], [Status]=โAuthorisedโ)))>>
What would be the formula to SUM that and the data from AppQuotesParts ?
<< SUM(SELECT(AppquotesParts[Cost] , AND([JobID]=[_THISROW].[JobID], [Status]=โAuthorisedโ)))>>
Much appreciated
*** UPDATE***
This is looking good:
<< SUM(SELECT(Appquotesitems [Total_11] , AND([JobID]=[_THISROW].[JobID], [Status]=โAuthorisedโ)))+ SUM(SELECT(Appquotesparts [Total_11] , AND([JobID]=[_THISROW].[JobID], [Status]=โAuthorisedโ)))>>
Unless you guys advise me differently but it seems to be working ??
Either that or if you are using a table in Word, then you can just add a formula to the Total cell to SUM(ABOVE).
Thanks Alex
I am still uncertain of how to do that. Does the cell have a reference or name so I can simplify this or should I just stick with what I have?
Thanks
Donโt do the calculations in the spreadsheet as suggested by @AlexM.
Thanks Steve, I stuck to the original without calculating in the Word Doc, it worked so I left it as it was.
I am very greatful for all the support from everybody always.
Kind Regards
Itโs not the spreadsheet I was sugesting, it was in the template.
Thats interesting and probably where crossed lines came from. I was referring to the template and Steve mentioned the spreadsheet.
The solution does work as it is so quite happy to leave it as is, but the possibility of formulaโs in the word template could prove beneficial later.
So I thank both of you guys.
Kind Regards
Thats interesting Alex, thank you.
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |