Hi All
I'm trying to do something which I thought was simple using indexing and conditions but I cant get it to work.
My second datasheet is supposed to pull only selcted rows of sales opportunity data into a commission sheet based on conditions such as salesman (using Initials) and whether an opportunity was converted into a sale.
I have enclosed an example of the data and the output I would expect to see.
If anyone knows how to do this it would be a big help.
Thanks
PJR
Secondary sheets are truly not needed. You can get the same results with no additional plumbing/maintenance by simply turning on the Data > Filter for the first sheet. Once that is on, filter column E for "contains MB" and voila! Same list. Done. You can edit, print, copy, whatever. No need to deal with the added baggage of additional sheets. This is what the Autofilter was created for.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi
Thanks for this. Unfortunately its not a solution to my problem as it would allow the sales person the ability to see everyone else's earnings by just changing the filter. It needs to be information which is only applicable to them and their sales.
The data is dynamically ceated for another workbook and I have tried protecting the sheet disabling the ability to change the filter however this does not work on new dynamically created data.
Any further thoughts?
Personally, I would never hand a person a copy of a workbook that had data in it (hidden or not) that they weren't ALLOWED to see at all. Excel is by no means secure. Most users, fine, they can be controlled, but all it takes is the *slightest* bit of interest and boldness and a few Google searches and everything in your workbook will be unlocked.
If you must hand out information to individuals that is an excerpt, it should be truly excerpted, and not by formula. I would print a PDF of each agent's rows of info from this main sheet (again, no other sheets needed, all done from this sheet with a Filter) and give them the PDF.
I understand the point you make however the workbook which dynamically creates the rows of conditional data is secure and it is impossible for the individual to open the original (something I'm very familiar with). The example workbook enclosed with the datasheet is an example of the datasheet we have on a secure server. It is purely the simple conditional indexing I was having an issue with as it was pulling in all the data and not the selected salesperson. I thought a fresh pair of eyes might help me find a quick solution.
I understand if this is not something that you consider pratical or worthwhile however it is to our business. Real time sales and payment details are essential to our sales team.
Thanks for your time in responding to my question anyway.
How about this?
Change the name of sheet to choose the salesman.
Or copy to the new sheet and rename it to new salesman
Quang PT
Please see attached file. There is a new sheet named "MB" with formulas. To change the the name of the salesperson simply enter new initials in cell M1
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks to you all.
A special thank you to both bebo021999 and AlKey. Both solutions worked fine as the sheets can be password protected.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks