Excel copying rows with two conditions

[Closed]
Report
-
 Ash -
Hi

I'm trying to create a dashboard - which will automatically pick up rows based on a condition. I.e. copy the row if its 'closed won' and if it belongs to 'John Smith'

I've googled this but I'm not that advanced in using excel - so don't understand some of the 'answers' i've seen.

Ideally then - once i've got all my sheets i will then have a control box which will mean I only have to insert the name of the individual to find out their activity.

any help appreciated.

Ash

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am sorry but I am not following you. In you sample you have a sheet called RAW DATA. There is a column K which has that "'closed won'" and other stages. Now what you want to do now? By Name what name you meant. also where the data needs to go. You need to explain in detail as I have no idea how the data should be read and move around etc
Hi

No problem.

Okay so each tab has a section i.e. Closed Won, Closed No Deal, Top Ten Opps by TCV.

I'd like to know how to take the data - from the raw data by the stage Column K (i.e. Closed Won to the Closed Won tab) - copying the row of data across to the relevant tab.

Then the next tab -Ii'd have the Closed No Deal stage to copy across the rows.

By the Top Opps TCV tab - I'd take the largest value on Column H (Total Contract Value (TCV) (converted)) across to that row into the relevant tab.

All this would then be controlled by a formulae of some kind that would allow me to enter the name of the rp manager (reporting manager) so that when I entered the name all the data would refresh in the above tabs to show only what that individual team is doing.

Making it much easier for me to run my reports.

Does this help explain?

Thanks
Ash
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
These seem to be your sheet name where data would be copied

Opps Past Due Date
Opps Won
Opps Lost
Opps No Deal
Opps Entered in Error
Top 10 Opps by TCV
Top 10 New Business Opco TCV
Top 10 New Business Rev This FY
Top Wins New Bus Rev this FY
Closing this Week by Opco TCV
Top Opps this Mth Opco TCV
Top Opps closing Qtr Opco TCV
Top Opps Closing FY Opco TCV


Now the question is that do these sheet names is same as the stages and by same I mean literally they are same
Hi

Not always - so for instance:

Opps Past Due Date - would be opps that have a decision date prior to the current date
Opps Won - would be stage as Closed Won
Opps Lost - would be stage as Closed Lost
Opps No Deal - would be stage as Closed No Deal
Opps Entered in Error - would be stage as Entered In Error
Top 10 Opps by TCV - would be Top 10 Opps by largest TCV Value - ie. column H (excluding, closed Won, Closed No Deal, Closed Lost and Entered in Error Opps)
Top 10 New Business Opco TCV - would be Top 10 Opps by Largest New Business Value - i.e. Column F (excluding, closed Won, Closed No Deal, Closed Lost and Entered in Error Opps)
Top 10 New Business Rev This FY - would be Top 10 Opps by Largest Annual Number Value - i.e. Column U (excluding, closed Won, Closed No Deal, Closed Lost and Entered in Error Opps)
Top Wins New Bus Rev this FY - would be Top 10 Wins by New Business i.e. Column F and stage Closed Won
Top Opps Closing this Week by Opco TCV - so Opps closing within the next 7 days - taken by Decision Date and the ones with the largest TCV value in Column H
Top Opps Closing this Month by Opco TCV - so Opps closing within the next 30 days - taken by Decision Date and the ones with the largest TCV value in Column H
Top Opps Closing this Qtr by Opco TCV - so Opps closing upto June 2010 - taken by Decision Date (this would then change in June to September 2010 - once this qtr is finished
Top Opps Closing this FY - and the ones with the largest TCV value in Column H
am open to suggestions on how to capture this data in another format - my intention is to be able to drop raw data into the raw data tab on a weekly basis and then format the rest of the tabs into ppt to the individuals concerned without having to create these manually weekly.