基本的A/P和A/R付款跟踪

RRABruce
RRABruce
编辑12/09/19 公式和函数

我们是一家拥有建筑部门的物业管理公司。我们的构造是基本的,只需要我们简单地跟踪

业主/地址/批准日期/工作编号(由我们创建)/承包商的投标/变更订单/向承包商付款/向承包商付款/余额/我们的投标/变更订单/向我们付款/应付给我们的余额/已支付现金的A/R /到期或已结束的工作。

我们目前使用的是Google Sheets,但是我们遇到了困难。每个工作都有一行,但这一行必须出现在多个选项卡上,因为支付给承包商的金额和支付给我们的金额,以及更改订单的成本,都在不同的选项卡上进行跟踪。

因此,我们有显示作业摘要的General选项卡

我们有承包商付款选项卡,其中我们列出的日期和金额,每一笔付款给承包商,其中总和在一般选项卡上,以显示支付给承包商的总金额,然后从金额在投标单元格在一般选项卡上,以显示余额应付承包商在一般选项卡上。

然后我们有一个收据选项卡,其功能与承包商付款选项卡完全相同,但是在一般选项卡上输入的金额总和显示我们收到的金额,然后从一般选项卡上的出价单元格中减去,显示一般选项卡上的到期余额。

这太乱了。因为一般选项卡上的行必须与其他两个选项卡上的行完全匹配,否则我们的收入和费用将被取消。当作业完成时,我们还会遇到一个问题,因为如果我们想要从常规选项卡中删除作业,我们必须对付款和收据选项卡执行相同的操作,但随后会丢失数据,因为所有内容都绑定在一起。应付账款的到期A/R也是一团乱,因为我不知道如果我们收到的金额大于我们支付的金额,如何让单元格不创建那个数字(因为当前的公式在这种情况下显示为负数)。

我在这里寻找答案,但不是真正的解决方案。

我想有一个表,其中更改的一般选项卡创建的行在其他两个或三个选项卡,我们需要。

我很想能够识别一个工作完成,并有它从一般选项卡中删除,存档在其他地方供参考,而不要求我改变整个工作表的景观。

我很想能够悬停在支付给承包商单元格,看到支票日期和金额弹出在一个自动填充的评论/注意

我想能够悬停在从所有者单元,看到检查日期和金额弹出在一个自动填充的评论或说明。

如果有一个复选框表明作业处于活动状态或已关闭状态,可以删除并存档作业数据,那就太好了

“作业编号”单元格会根据公式自动创建作业编号,这很棒。

有单元格用于对主标题进行汇总。

我有一张原稿可以分享。

让承包商从下拉的联系方式将是伟大的。

我不需要管理项目、时间线、日期、副标题、材料或其他任何东西。我只需要跟踪我得到批准的时间,谁在做这项工作,他们期望多少,我批准的出价,我付了多少钱,我得到了多少钱。

有人有这样的模板吗?

承包商sheet.JPG

评论

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
[Date of Site Survey (Product)]@row, \"yes\", \" \")) Intended logic\/result - If date cell is blank, then \"blank\" (this worked when I tried without the 2nd argument added) If date is in…","categoryID":322,"dateInserted":"2023-06-09T14:00:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T23:14:36+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T20:05:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-09T23:38:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3372673501,"url":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","format":"Rich","lastPost":{"discussionID":106265,"commentID":379953,"name":"Re: If Date is blank + past formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379953#Comment_379953","dateInserted":"2023-06-09T23:14:36+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-09T23:38:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T14:28:35+00:00","dateAnswered":"2023-06-09T14:12:11+00:00","acceptedAnswers":[{"commentID":379830,"body":"

Try this:<\/p>

=IF([Date of Site Survey (Product)]@row <> \"//www.santa-greenland.com/community/discussion/17781/\", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \"no\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106277,"type":"question","name":"Help creating formula to update inventory automatically","excerpt":"The following is our brochure requests sheet. Right now we manually update our inventory each time we receive a request. Here is a snapshot of the new inventory sheet. I'd like to create a formula that automatically subtracts 100 from Spanish Qty each time the Request Completed is checked, the Request Denied column is…","categoryID":322,"dateInserted":"2023-06-09T16:17:37+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T18:12:07+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T18:07:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161705,"lastUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T18:07:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":30,"score":null,"hot":3372664184,"url":"https:\/\/community.smartsheet.com\/discussion\/106277\/help-creating-formula-to-update-inventory-automatically","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106277\/help-creating-formula-to-update-inventory-automatically","format":"Rich","tagIDs":[204,219,254],"lastPost":{"discussionID":106277,"commentID":379911,"name":"Re: Help creating formula to update inventory automatically","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379911#Comment_379911","dateInserted":"2023-06-09T18:12:07+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T18:07:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/FMANSEYA9OS3\/mm-brochures-request-sheet-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"MM-Brochures-Request-Sheet.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T18:11:32+00:00","dateAnswered":"2023-06-09T17:51:51+00:00","acceptedAnswers":[{"commentID":379899,"body":"

@Christina S.<\/a> <\/p>

Switch your formula to:<\/p>

=[Spanish Qty Baseline]# - (COUNTIFS({Brochure Requests Sheet Request Completed}, 1, {Brochure Requests Sheet Request Denied}, ISBLANK(@cell), {Brochure Requests Sheet Brochures Confirmation}, CONTAINS(\"Spanish Brochures\", @cell), {Brochure Requests Sheet Submission Date}, >DATE(2023, 6, 8)) * 100)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":204,"urlcode":"Forms","name":"Forms"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106270,"type":"question","name":"Completely stumped on this formula","excerpt":"Hello, I'm trying to be able to identify when an upsell comes through for a specific function. But so far everything I've tried hasn't worked. The sheet I want the information to show up on isn't the sheet that has the data so I have to reference a separate sheet. But basically what I'm trying to do is: =IF(AND({Customer…","categoryID":322,"dateInserted":"2023-06-09T14:28:33+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T19:58:02+00:00","insertUserID":151459,"insertUser":{"userID":151459,"name":"Paige_Hamby","url":"https:\/\/community.smartsheet.com\/profile\/Paige_Hamby","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-09T19:56:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":151459,"lastUser":{"userID":151459,"name":"Paige_Hamby","url":"https:\/\/community.smartsheet.com\/profile\/Paige_Hamby","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-09T19:56:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":29,"score":null,"hot":3372663995,"url":"https:\/\/community.smartsheet.com\/discussion\/106270\/completely-stumped-on-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106270\/completely-stumped-on-this-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106270,"commentID":379927,"name":"Re: Completely stumped on this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379927#Comment_379927","dateInserted":"2023-06-09T19:58:02+00:00","insertUserID":151459,"insertUser":{"userID":151459,"name":"Paige_Hamby","url":"https:\/\/community.smartsheet.com\/profile\/Paige_Hamby","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/n7ZG0TBD44T33.png","dateLastActive":"2023-06-09T19:56:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T19:58:20+00:00","dateAnswered":"2023-06-09T19:39:44+00:00","acceptedAnswers":[{"commentID":379924,"body":"

@Paige_Hamby<\/a> <\/p>

I will admit, I was \"Completely Stumped\" for a moment as well... but I believe this should work.<\/p>

=IF(COUNTIFS({Customer Name}, Customer@row, {Document Type}, \"Upsell\", {Item Purchase}, HAS(@cell, \"SummitIT Upsell\")) > 0, \"Upsell\", \"//www.santa-greenland.com/community/discussion/17781/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势