Auto Xfer Specific Data to a New Wkst

LSL18 Posts 2 Registration date Thursday January 31, 2019 Status Member Last seen January 31, 2019 - Jan 31, 2019 at 12:36 PM
 Blocked Profile - Jan 31, 2019 at 01:54 PM

I have a raw data file of sales opportunities and would like to automatically identify and copy the TOP 10 Optys onto a new Wkst. Further, I would like this to become a template wherein I can add opportunities to the raw data worksheet overtime and still have those TOP 10 Optys continue to be auto identified and copied onto the designated new Wkst (so only 10 total optys remain).

Is anyone able to assist?

Thanks a bunch!

1 response

Blocked Profile
Jan 31, 2019 at 12:48 PM
How are ypu qualifying the top opportunties?

Do you have any Access experience? If not then you are at the same knowledge level as with the excel program, except Access is for maintaining records in a scaleable solution! Where excel is for calaculating complex math problems. Let us know if you need help with table structures.

If you insist on using excel, then I encourage you to record a macro of what you want to happen, then edit it with values that are variables. If you need help with that, post your maceo jere for review.

LSL18 Posts 2 Registration date Thursday January 31, 2019 Status Member Last seen January 31, 2019
Jan 31, 2019 at 01:00 PM
I'm qualifying the top opportunities 1st by revenue, 2nd by length of sales cycle (# of days since created on a sliding scale) - I've created separate columns that calculates both this algorithm as well as ranks the opportunities to reveal the Top 10.

Initially I hoped to achieve this by way of a simple VLOOKUP, and its doable, but there's still elements of manual work in having to check/re-check the data and outcome with every refresh and frankly was hoping to spend the time now on building a tool that I'd never have to touch again, allowing those who needed the info simply to refresh the data (which is built off of a dynamic existing connection).

I have some Access experience, literally a graduate class a few moons ago but no relevant use of that knowledge since...

I'm thinking what I have is as good as its going to get.

Appreciate the swift response.
Blocked Profile
Jan 31, 2019 at 01:54 PM
Well you could build the sales tables in access, then deploy data connections from excel clients to make the qualifying queries based on your standards. Then you could have modified datay connections depending on what region or torritory the salesperson works.

It would seem to me that data entry, then reporting on the data through modified queries would be the best solution for a stable model. It would eleinate the possibilty of deleting formulas or some sort of disastor when moving the cells. Let us know what you think about that model.