I'm creating a sales pipeline in an excel workbook and there is a tool I would like to use but I can't seem to crack it.
I have multiple worksheets, the first one is a summary to be viewed by sales director and the other ones are sheets for each individual sales person. Each salesperson has a specific worksheet where he adds info on the client, deal size, past action next action and so on. Each client has its own row, and leads are ordered by quarter.
The complicating aspect is that the summary sheet is password protected and not accessible to the salesperson.
I would like to have a macro that links and tracks the info in each row and links it to the summary sheet so that any info added in any of the individual sales worksheet is automatically updated in the summary worksheet.
I have tried linking the cells between each other but it becomes problematic when the salesperson needs to insert a row into his worksheet (precisely because the salesperson cant access the summary sheet). To circumvent this I wanted to create a macro that when used inserted a new row in the salesperson's sheet, inserted a new row in the summary sheet and linked the cells between both sheets, but excel wont let me link cells in a macro.
Do any of you have a workaround for this problem?
Many thanks for taking the time to read!!
答案1
I too would use a pivot table with dynamic ranges/tables as the data source. You may have to refresh to get the most recent updates. One concern I have with this approach is the potential for data loss if one or more of the source spreadsheets are not available. Excel's documentation should help with that.
Your end result is a common need in business. So another option is to look into free sales pipeline tools. One I found was an Excel spreadsheet from Hubspot, that would keep you using a familiar app. You could investigate options on a wiki like Sales and Marketing Stack.