Update Dasboard using VBA.

I understand it is a Saturday, but any chance I can get any help on this today.

Sorry in case I’m being impatient. This is my first post and assignment so very keen to accomplish it.

Bump no response Help please!

Dearest Nilem,

This is superb, mind blowing, out of this world. Thanks a ton

From what I've tested thus far it is all good. A couple of things:

Can the VLOOKUP in Column B and C be added in the code. This will ensure that I don't need to drag it every time. My data is immensely usage. with it increasing by every day

Please refer to my post 11 for explanation on the requested output for Columns R & S

Also can you please add something in the code to clear the data from the dashboard before every run.

Thanks a lot.

Explanation on the requested output for Columns R & S:

Report 3 - For every single rep in Column O

Condition 1: If Column D = Bad
Condition 2: If Column X = Benefit

Divide the count of Pending (in Column AA) by 2 for the rep (column O) and add it to the Fixed count.

Let's take an example:

If Cynthia's original Fixed and Pending count is 4 each. But if the above condition becomes applicable the data in R & S given that 4 Bad (in column D) and 4 Benefit in (column X) should be updated as follows:

4 Bad in column D along with Benefit as the status in column X divided by 2 = 2

New result should be R = 6 and S = 2. Technically speaking if the above 2 conditions are met then the Fixed count is increased by adding 50% of the Pending ones to it.

Hope this makes it more clear!

Sure, give me sometime and I'll post a sheet with some examples. All I need is to divide the pending count by 2 wherever the 2 conditions stated above are met and add the 50% of it to Fixed in columns R and leaving the other 50% in S.

Thanks!

Here's the file with some modified data and the much needed result in Columns R & S highlighted in yellow of the Dashboard. Please refer to the Report 3:

Column D has the BAD
Column O has the Rep name
Column X has Benefit
Column AA has the result = Fixed or Pending

Hope this makes it all clear. Thanks you and sorry for any confusion!

Thanks a ton. I'm testing it now. Will let you know in a bit if there are any issues. I doubt there would be any. Thanks for this immense help!

Dear Nilem,

Awesome is a very less word to define what you’ve helped me with.

During my several tests today 2-things that I came up and request your expertise are:

These are new observations / challanges. The solution you supplied is just too good.



Related Tab: I notice there were some reps that were not available on this tab, but there is a data for them on either Report 1, 2 or 3. I’m not able to identify such cases. This is something to do with my data. There are huge number of them getting added and a chance of these getting missed. To counter this..

1. Is it somehow possible to add a logic to the code that can pick such cases from Report 1, 2 and 3 and paste them in a separate tab and say Not available in Related? It would be immensely helpful.
2. Lastly, there are several columns on the Dashboard (F, G, J, K, L, M and N). Can the code somehow drag these formula to the last line of data after the macro is run (similar to what is being done for vlookup column B and C).

Other than this every bit of it is working “Perfectly”

Appreciate the help!

Fantastic !! Thanks a bunch for all this. You are simple amazing…

I’ll do a final round of run on this tomorrow and get back. This will surely make my life simple and avoid losing unnecessary time.

What a solution…I’m speechless. Thank you!

Simply beautiful if I can say the least!

2 minor observations. Okay if these can't be done, but thought to check:

The missing ones that are getting pointed out in Column G. Can these tend to appear only once if they are missing from a report. What is currently happening say if a rep Jason has 10 entries in Report 1. He is appearing 10. Ideally I would only need his name once for identification purpose, but if he is also missing in 2 and 3 then it should be pointed out once per report. Also the report name can be moved to Column H. It is currently appearing as Jason-Report1. I would prefer having it as Jason in G and Report1 in H.

Lastly, can these missing ones also automatically get added to Column A of related, but do remain in G as well.

My last test is in progress, and my next step would be to close this thread. It has been solved to utmost perfection. Never imagined people help like this. I'm absolutely delighted and humbled for this help by you Nilem!

Ooops one more thing can a trim function be added to remove unwanted spaces before and after the names being used for mapping across the 3 reports. This I suspect is creating an unmatched entry. Juts because there is a space before or after the name.

Sorry to trouble you so much. Hope you'll understand!

Simply WOW! I'm so sure of how this is going to worl flawlessly that this last version is going live. I'm would like to extend a humble thank you and for making experience of my first post so GOOD:-)

Greatly, thankful!

Пожалуйста

(You are welcome!)

What does this mean? I know only english. Sorry!

Can I add you as a friend?

Ohh...Got it. Thought as much You are a superstar!!!!

Dear Nilem,

Trust you are doing great!

I need a little advise due to an unexpected twist in the last bit of my request where we were giving a benefit of 50%.

The only thing that is required is to be able to ROUNDUP.

Example: If there are 3 Pending. Going by the logic it should be 3/2 = 1.5 and the final values should be rounded-up i.e. 3 Pendings should become 2 Fixed and 1 should stay as Pending, as against earlier where it was getting updated as 1 Fixed and 2 Pending.

Sorry to trouble you but the authorities just changed the scope and hence the change.

Where exactly and what change will be need to made please advise.

Thanks a million!

Awesome, thanks! I'll try and let you know if there are any issues.

Originally Posted by avid2xl

Dear Nilem,

Trust you are doing great!

I need a little advise due to an unexpected twist in the last bit of my request where we were giving a benefit of 50%.

The only thing that is required is to be able to ROUNDUP.

Example: If there are 3 Pending. Going by the logic it should be 3/2 = 1.5 and the final values should be rounded-up i.e. 3 Pendings should become 2 Fixed and 1 should stay as Pending, as against earlier where it was getting updated as 1 Fixed and 2 Pending.

Sorry to trouble you but the authorities just changed the scope and hence the change.

Where exactly and what change will be need to made please advise.

Thanks a million!

Dear Nilem,

In the above scenario while running the report today I dicovered everything was happening as expected other than cases where there was just 1 pending. It was not getting converted into Fixed after. Applying the 50% logic.

Could you please advise of the change I should make in your fantastic script to ensure that gets calculated correctly.

Sorry for the trouble and thank you!

Jason

Dearest Nilem,

I await your kind help on this. Please help me!

Thanks and sorry for trouble.

Regards,
Jason