Best approach for top sales deals?

[maverick15]

Hi -

Thank you in advance for the help. I'm working on a sales runbook that combines actual and pipeline data to get to a forecast for the quarter. The sales leaders need to be able to determine, at a specific opportunity level, if an opportunity is going to close this quarter or not. If it will close, we'll take the "full" amount of the deal, if it won't close, we'll set the amount to 0, and if it might close, we'll take a weighted amount (specific field).

Right now I've been managing this by creating a pivot table of the top deals, then copying the data to another tab where I'm showing the pivot table info plus having a column where they can add a Y/N to each row. I think do a VLOOKUP on that row and update a column in the backend data, which adjusts the call $ amount based on the Y/N value (blank just takes the weighted amount).

I'd really like to use the original pivot so I don't have to keep copying/pasting each week when we update the data, however if they filter the pivot, the rows change and any comment they put in gets mixed up.

The workbook is being edited in Teams as well as in Excel, so I don't think VBA is an option.

I'm moving to using a Data Model for the backend data, but can have the source data "call" column update via the lookup method I'm using.

Is there a better way to do this? Any suggestions?

Thanks!

 

[ask2tsp]

This smells like a Power Query job to me. Build a table with the top n deals and a blank Y/N column.