Forum Discussion
innovative way to display data
First let me ask, since I don't read Arabic, whether those are real names: if they are, since there are other people who read Arabic (presumably including you), you should remove that file from the site. Please also, put in names of movie characters (Luke Skywalker, Mickey Mouse, Minnie Mouse, etc)
Second, you've not really answered my question. I fully understand the desire for clarity, I know what that means. But I'm not familiar with this kind of data, or the kind of report you're charged with creating. Your answer, "i need to creat some reports for aging and customer balances and any other useful reports to tracking the outstanding client dues" assumes that I would know what those terms mean. I have some ideas, but again, you're asking me to guess, especially with "any other useful reports"--what is "useful"?? Not the meaning of the word--I know that--what would be an "other useful report" in this field?
As I said, if we were sitting down face-to-face, I'd be asking these questions and we'd be able to move forward quickly. You need to assume the person you're writing to (on a site such as this) is knowledgeable about Excel, but an utter novice when it comes to the specific kind of data and report that you're working with. That's your expertise; you need to do some translating.
Dear Mr. Mathetes
i'm sorry for the inconvienient, let me explain more to make that issue clear.
I have a list of different units’ column (B) that sold with list of amounts column (D) with different payment date column (E)
my input is entering the payments only and for the first columns (A: E) without change still as it is
Here under explanation of the attached sheet I hope to be clear for you
1st I have a list of dues per date per unit
Unit Due Date
NE04-B-1702 25/01/2019
NE04-A-0506 16/03/2019
NE04-A-0506 16/06/2019
NE04-A-0506 16/09/2019
2nd I have a list of collection in columns that covered the above dues that paid in different date
Payment_1 Payment_2
COLLECTED CLEAR DATE Rec# COLLECTED CLEAR DATE Rec#
160,000 02/04/2019 5006 61,598 09/04/2019 5008
3rd calculated field (BE) that refer to the outstanding dues which = amount in column (D) - total collected in column (AW)
My question is
Is there any way to present the above date to be clear or understandable for anyone?
I hope you got my point.
Thanks
You wrote:
My question is
Is there any way to present the above date to be clear or understandable for anyone?
I hope you got my point.
And yes, you made your point. But the thing I don't understand is why you--the specialist in this field--need to ask "is there a way to present the above data to be clear for anyone." You would be expected to lay out the data in a way that's clear, not just pointing to the pieces of data. I can tell you how to use excel to take data elements from your spreadsheet and array it in a manner that's clear, but I need YOU to tell me how that data could be arrayed so as to be clear and understandable to anyone. Not just point to the data and ask if it can be done.
So my question back to you: is there any way for YOU to create a dummy display demonstrating how the data could be arrayed so as to be clear and understandable. I'm NOT asking you to come up with the Excel instructions, that the job of the Excel experts. But I am asking you to create a clear and understandable display of the relevant data, doing it manually....I assume you want this to be for your clients, or for a customer service rep or the like. What would be helpful to them, how would it be arranged. I think the Excel part is going to be fairly easy.....but you need to be the one to come up with at least an initial creative and clear display of the information you want to show.
i put some reports in the attached file
- Sheet (Dues) i need to add formulas to sum the total collected (F) from sheet collection details and settle the first dues based on earliest dues till consume the total collection, also i need to calculate the culumns from (H:L) based on the due dates and the monthly closing date lets say 31-03-2022
- Sheet aging for ar balances, i need to calculate the Ar balances (sheet dues Culumn G) and the critira range will be project culumn (A) and the due date (E)
- Sheet Delayed AR balances to calculate the delayed dues (AR balance in sheet dues_G) based on the due date in sheet dues
- sheet unit reprt i need to filter by unit to show all details related to the unit.
I know I'm bothering you, but I need help, even with an idea
Thanks You
hussein_elsayed Let me know if this set of results makes sense, if the numbers are what you'd expect.
I am using the new FILTER function which requires the most recent version of Excel.
My guess is that you'd be well served in the other reports as well with the FILTER function. Here's a YouTube Video that explains it. https://www.youtube.com/watch?v=9I9DtFOVPIg