Forum Discussion
help with connecting 2 worksheets with dates and amonuts
Hello, thank you so much.
Here's the link: https://docs.google.com/spreadsheets/d/1oTKLa-zww2dSkj1POaU7QNiL-Uk0BtnJ/edit?usp=sharing&ouid=114934743106846975786&rtpof=true&sd=true
I think I understand what are you trying to say, but still can't visualize how to do it.
Well, after looking at your file at Google, I'm going to turn your last sentence--"I think I understand what are you trying to say, but still can't visualize how to do it."--and turn it around: "I can visualize what you ARE doing, but I still can't understand what you're trying to accomplish."
Also, it's a "View Only" file, so I and others here can not do anything with it other than view it.
Please put in a bit more information (or add some description here in a post) on what each column represents (doing it in English, if you don't mind). What is the relationship between the Master sheet and the yearly ones, as you have it set now?
In general a much more complete description of the business purpose and the various process steps by which data gets collected and entered would be helpful.
- Here it is: https://docs.google.com/spreadsheets/d/1oTKLa-zww2dSkj1POaU7QNiL-Uk0BtnJ/edit?usp=sharing&ouid=114934743106846975786&rtpof=true&sd=true
Found it - it's on editor now.I'm really struggling to get my head around what this is all about. My sense--which may not be correct--is that you're approaching it, in the design of your workbook, in a way that makes it far more complicated than it really is.
Could I ask you, therefore, to step away from spreadsheet design--and from working to explain your sheets and columns--and to just describe the business itself, the work flow, the cash flow (whatever your preferred label might be).
What I understand (but would like clarified) are such things as:
- you're tracking payments coming in
- needing to keep under (please explain why "under"; is this for tax reasons) a given amount. Why not just "the goal ultimately is to pay in this account a total of 250.000€"?
- is that 250.000€ a part of a much larger amount owed by the payee?
- How many accounts do you have? How are they identified?
- (Approximately) how many accounts are going on concurrently?
- Does each account have negotiated payments toward a final total bill?
- Do those amounts vary, or are they consistent for each month or quarter (or other period)?
And so forth.
Those questions were just meant to get started with a description, not to be an exhaustive list. Imagine that you're explaining the business itself to a new employee, someone who is quite bright, but who needs to have the process explained.
- DON'T spend time explaining your spreadsheet.
- DO spend time explaining the business and cash flow.
Let me (or somebody else looking over our shoulder here in the forum) worry about the spreadsheet design. My strong sense is that you'll be able to accomplish your goal with a far FAR simpler design, but let us worry about that.
- My dear Mr. Mathetes,
Okay, I'll do my best to describe the “wants and needs” without breaking any company rules.
This is a company that provides services for its clients. Every service provided is followed by any kind of document which proves that service is provided. When my clients approve the content of that document, my service is considered fully admitted and accepted. In that moment, according to our contract agreement, my company issues an invoice for service provided.
Every contract agreement isn’t on the same amount, of course. Sometimes we charge 10 working hours, and sometimes less/more. We charge by the hour followed by timesheets.
My company currently has 3 bank accounts, which I described as “a”, “b” and “c” in the workbook. Later this year we expect to open another one, but as for now, there are 3 of them. Yes, taxes are the main reason. We need to be exempt from tax company so we could provide these services for those who are also exempt from paying taxes. A limit set on each Bank account for this text is 250.000 € (of course, that’s not the real amount but I cannot say the real one, so let’s go with 250k).
Regarding invoices – we issue them in template also created in Excel but I can’t use them to connect with my master workbook.
Description of my task is this: “create an Excel workbook with a list of every contract agreement between us and our client where we can keep track of every received payment and react on time to keep annual turnover under 250k.”
Since I work here long enough to know how my work colleagues think and work, my actual main task is to create a workbook and make it as much automatic as Excel can provide and lock the cells where formulas happen so my colleagues can’t change it. Therefore 10-year display at start.
My company doesn’t want any accounting program, so I’m limited to Excel which I love, btw 😊 But I don’t know how to do this one. If you can help, I’ll be most grateful. Thank you for your time. Really.
- Okay. I've opened and rearranged the workbook - found some mistakes; I think now is okay.
As for the View only - don't know how to make it editable but in properties, it stands that Viewers can download it..
So, I need to be able to keep track of payments in one calendar year. The goal is to keep incoming payments under certain amount - in this sample it's 250.000€.
So, in sheet 2022, columns O through S is where I wanted to keep track of payments. Important data to keep track are dates of payments and amounts.
Now, I'm struggling with how to place formula for those 2 conditions.
My idea is to separate:
- case 1: when I receive 1 complete payment where column R is set to tell me that both date of payment and amount is as scheduled,
- case 2: when I agree with the client for 2 or 3 payments where all 3 payments equals total amount agreed (column J) in this year,
- case 3: when I agree with the client for 2 or 3 payments where 2 payments are made in this year, and 1 payment will be paid next year.
If 1 payment in case 3 will be next year, I wanted to make the whole row to be automatically transferred to the next sheet, 2023.
The 3rd case is troubling me. What do I need to do to make it automatically transfer the whole row with all details with exception in columns O through S where:
- column O inherit the last known date of payment in year 2023,
- column P inherit the same properties as column P in sheet 2022,
- column Q calculates/summarize received payments in last year (2022) plus received payments in this year (2023) so that
- column R can reproduce "OK" notification, and
- column S calculates that there is no possibility that any amount could be paid next year (2024) for service agreed in 2022.
Master sheet however has to be able to show me summary of annual turnover on the business bank account.
- column A stands for years (want to make 10-year display)
- column B stands for annual amount limit
- column C can change over time (if payments goes in next year then next year's limit reduces for that received amount)
- column D shows me the status of received amount in the corresponding year
- column E calculates how much "room" do I have in the corresponding year (limit mustn't be exceeded)
- column F shows the same as D (forgot why I thought I needed this one...)
- column G shows the amount paid in corresponding year but comes from the year before
- column H shows the amount which will be paid in next year
- column I warns me if I'm close to defined limit