Dear All,
I am working an excel for Anti Money Laundering which maintains client data with dashboard.
I did as much as i can but i need assistance to formulate certain functions.
By any chance if anyone has such ready made template or willing to assist me, i would be glad to share.
Regards,
SJ
Last edited by AliGW; 07-27-2017 at 05:11 AM.
.
I'm up for it. Don't know how much I can help but I'm willing.
Let me know.
Thanks Logit, how can i send you the file?
If you remove the confidential information you can post it here.
GO ADVANCED / MANAGE ATTACHMENTS
Of I can send you my private email.
Let me know.
Keep in mind FORUMS like to have the project discussed on the FORUM so others can benefit from the knowledge. Hopefully you can remove any confidential information
and simply post it here.
Thanks Logit, for some reason i cant attach my file. May i request you to PM your email? If you don't mind.
Also i dont have any sensitive information. I have removed them, just dummy details.
No - the file needs to be shared here - iy is against the forum rules to go to private messaging.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I can't share, don't know why? how i do it?
Please find the attachment
The workbook you have provided is of little use as it stands as you have given no indication of the type of output you require - the dashboard tab is completely blank! Please manually mock up how you would like it to look.
Thanks Ali.
So sorry for not explaining much. Let me brief what is the output im looking at:
Dashboard Tab:
Chart and Table which illustrates High,Medium and Low client from "Client Information", Column R
No of clients due to be reviewed Based on the tab "Client Information", Column "X".
Client Information Tab:
Column "O" - Country, Based on selection made on Column "N" the rating should auto populate.
For e.g., if i choose "Malaysia" on column N, Column "O" - Country should auto populate the score based on Formulas tab.
Malaysia = 2.
Formulas tab - No changes
High, Medium & Low - Separates the clients from High, Medium and Low based on "Client Information", Column "R".
Also, i welcome any other suggestions from the expert.
My sincere apologies, i am trying to develop something with just an idea.
I would like to thank in advance for those who can assist.
Regards,
SJ
OK - so I still need you to create a manual mock-up of what you want the dashboard to look like - the layout is what I am after (you can use a rectangle for the chart, for example). The other things are relatively easy - I'll have a look now.
First, remove the data validation from column O. Now choose Malaysia in N2 and copy this into O2:Client Information Tab:
Column "O" - Country, Based on selection made on Column "N" the rating should auto populate.
For e.g., if i choose "Malaysia" on column N, Column "O" - Country should auto populate the score based on Formulas tab.
Malaysia = 2.
=IFERROR(INDEX(Formulas!$O$2:$O$220,MATCH('Client Information'!N2,Formulas!$L$2:$L$220,0)),"")
Drag copy down the column.
See if you can adapt this to auto-fill the other three boxes on that tab.
I have attached the Sample dashboard.
Each schedule maybe need a pie/bar chart.
Thanks Ali, let me try.
Let us know how you get on. Thanks for the new spreadsheet - it's nice and clear.
For your dashboard, these copied down:
Excel 2016 (Windows) 32 bit
Sheet: Dashboard
You are going to need to explain this:
Excel 2016 (Windows) 32 bit
Due Date No of Clients Due Date 30
Sheet: Dashboard
Sorry for late reply Ali.
Thank you so much for your help.
For the due date:
- Based on tab "Client Information", Column "X", the data will populate in Dashboard.
For e.g., if the due date is stated as 28/02/2018 and the no of client needs to be reviewed is 30.
Anyway to be done?
There is no data in column X of the client information sheet, so how are we meant to work out the due date and the number of clients?
err..sorry, let me work out. Ill send you the new excel.
Also, i manage to input ur formulas. If you see the dashboard, alots of low because its capturing based on the formula. How do i hide formula or will only reflect Low based on the result of "H", "J", "L", & "O".
Let me see the workbook and then I'll try to answer your question. Make sure it's the most up-to-date with all my formulae added.
Sure, if not you can scold me lol
Hahaha! Well, I am a teacher ...
Still waiting ...
Sorry Teacher was busy with work and trying to fix this formulas. :D
Teacher i got few more things that i need your help, if you can assist:
1) Dashboard, i need to generate a pie chart based on table. I will try.
2) On Dashboard, when you click on the information it should open another tab with information. For e.g., On Risk Rating,
when i click on high(1 count), another tab opens with the information of the client. How do it do that?
3) On Due date, the date should be formulated to follow the live date and based on the date, no of clients pops up.
The raw data is referred to Client Information Column "Z".
Teacher am i asking too much? If you can assist please do.
I feel bad though
1. Yes, you should be able to work that out, but if not, shout!
2. & 3. We are moving into the realms of VBA here, and I can't help with that - sorry. I'll amend your thread title and move this to the VBA section.
4. I really AM a teacher!!!
oh thank you so much teacher ! Hope i can get you a coffee if you are staying near by.
Thanks so much for the help.
Hopefully someone can help me with the VBA functions
Coffee woudld be nice, but I already have one and, in any case, I have no idea where KL is!!!
Hopefully someone will comment on the VBA possibilities soon.
Hahaha KL is in Malaysia ! Should pay a visit soon !
Ya i hope, i desperately need help. With your help i have developed something.
Thank you a million!
You're welcome!
Ah, Kuala Lumpur - got it!
Guys, anyone can help me with VBA? i have attached the excel, i want to see the below result:
1) On Dashboard, when you click on the information it should open another tab with information. For e.g., On Risk Rating,
when i click on high(1 count), another tab opens with the information of the client. How do it do that?
2) On Due date, the date should be formulated to follow the live date and based on the date, no of clients pops up.
The raw data is referred to Client Information Column "Z".
Please help
You need to be patient. Please remember that this is not a paid 'service': those who help here do so voluntarily and out of their own goodwill, and they are in many different time zones. The person who can help you may not yet be awake.
Oh no, not my intention to chase but to put it on a clear topic as we have many pages.
Not really chasing anyone.
By any chance i can close this topic and re-open a fresh one? Maybe that will help to maintain in a new document.
No, you may not, and there is no need - no benefit to be had by doing so. There is a lot of relevant detail in this thread. It doesn't matter how long the thread is, and it is in the correct place. I will say again (for the last time, I hope): BE PATIENT!
oh dont scold me teacher
Well, stop asking, then! I have explained already that you are not allowed to open a duplicate thread. It doesn't matter how many times and ways you ask: the answer is "no".
Oh yes I WILL WAIT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks