Hello, pls help. My workbook contains data for payment tracking. Tricky time is end of the year when some payments scheduled for this year happens in next year. Worksheets: 2022, 2023, etc. Sheets must be connected with dates and amounts, e.g.: my client pays upfront 5.000€ on 15.11.'22., and final payment of another 5.000€ will be on 15.01.'23. Now, every payment which happens in next year becomes a revenue of that year and must be shown in sheet 2023. Every received payment's entered manually. I need formula or such which can help me track payments in both years and also notify me that there's payment that happens in next year. I need this for work and can't move on until this is solved. Can anyone help please? And also - I have sample workbook for you to see, but how do I attach it??

Ana_M1212 I strongly suspect, from reading your description, that I will be inclined to suggest you maintain a single database (i.e., don't separate transactions by year; just have them dated) Excel can handle single databases very well, doing its own separation based on date fields in each row. We get in the way when we create separate sheets by year or month--those might make sense when you're keeping paper ledger sheets, but separating like that interferes with Excel's abilities. But let's take a look at what you've done. And also - I have sample workbook for you to see, but how do I attach it?? You'll need to place it in OneDrive or GoogleDrive and share the link. Make sure you do it in a way that it's not "Read Only" -- so use a copy, so that those of us offering advice can also test what we're suggesting.

mathetes 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.

Ana_M1212 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.

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

Here it is: https://docs.google.com/spreadsheets/d/1oTKLa-zww2dSkj1POaU7QNiL-Uk0BtnJ/edit?usp=sharing&ouid=114934743106846975786&rtpof=true&sd=trueFound it - it's on editor now.

help with connecting 2 worksheets with dates and amonuts

mathetes
Silver Contributor
May 19, 2022
Ana_M1212

I strongly suspect, from reading your description, that I will be inclined to suggest you maintain a single database (i.e., don't separate transactions by year; just have them dated)

Excel can handle single databases very well, doing its own separation based on date fields in each row. We get in the way when we create separate sheets by year or month--those might make sense when you're keeping paper ledger sheets, but separating like that interferes with Excel's abilities.

But let's take a look at what you've done.

And also - I have sample workbook for you to see, but how do I attach it??

You'll need to place it in OneDrive or GoogleDrive and share the link. Make sure you do it in a way that it's not "Read Only" -- so use a copy, so that those of us offering advice can also test what we're suggesting.
- Ana_M1212
  Copper Contributor
  May 19, 2022
  mathetes
  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.
  - mathetes
    Silver Contributor
    May 19, 2022
    Ana_M1212
    
    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.

Forum Discussion

help with connecting 2 worksheets with dates and amonuts

Share

Resources