Pasting data to first available cell in other worksheet.

  • Thread starter Thread starter craigie
  • Start date Start date
C

craigie

As a user of Excel in it's most basic context (LOOKUP's are a problem t
me!), I'm trying to create a sales funnel and am having nothing bu
problems!

Each worksheet will contain data in rows representing each sale
opportunity. The funnel will work with separate worksheets representin
each stage of the sales process. My questions are this:

1. How can I automatically cut a row and paste it to the firs
available row on the next worksheet, when recognising the wor
"Promote" in a particular cell.

2. How do I create individual reports, showing the total value o
possible sales at each stage of the funnel?

3. How can I track the number of days a particular opportunity has bee
positioned at a particular stage of the funnel?

4. how can I produce a visible funnel graphic showing no. o
opportunities, value of opportunities, average days at each stage?

I would very much appreciate some help with this most basic o
requests
 
Hi
first question: Are you sure you want to use Excel for this. Not sure
how complex your sales opportunity management is but these requirements
sound more like:
- using a small scale CRM solution
- using a database (e.g. MS Access) to store your data.

If it has to be MS Excel I would design the spreadsheet as follows
(just a personal opinion - feel free to adapt what suits your
requirements):

1. Create a master table or storing ALL your data. This sheet should
consist at least of the following columns:
- Opportunity ID to identify and link opportunities in different
stages
- Description and other columns to describe the sales opportunity
(customer name, key accounter, contact persons, etc.)
- Opportunity Status. This status in combination with the
opportunity ID should be unique (that is for each opprotunity ID a
status is
exists only onced in your data). Possible values could be: Open,
work in progress, customer contacted, negotiation, close with
success, close without success, pending, etc (you should define a
kind of status flow to identify all possible stages of your
opportunity)
- Date: Date on which this opportunity status was 'achieved'
- Due Date
- Responsible person
- You may have a look at the following add-in for data entries:
http://j-walk.com/ss/dataform/index.htm

2. You may consider using data validation listboxes to select valid
entries (and only valid ones) from a listbox. e.g. for Status,
Responsible, customer, etc.. See
http://www.contextures.com/xlDataVal01.html for more information

3. Now you may use filter, sorting, etc. to create different views of
your data. You could use advanced filters for copying specific parts of
your data to a separate worksheet (e.g. all closed opportunities). See
the following sites for some starting information:
http://www.contextures.com/xladvfilter01.html
http://www.contextures.com/xlautofilter01.html
http://www.contextures.com/xlSort01.html

4. You may also consider using conditional formats for highlighting
specific records (overdue, etc.). See
http://www.contextures.com/xlCondFormat01.html
http://www.cpearson.com/excel/cformatting.htm


5. For reporting purpose you may consider using pivot tables:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html


------
Now some drawbacks of Excel for creating this kind of application:
- difficult to change the status of an opportunity. Using the aboy you
have to manually enter a new record with the new status. No checking if
this status is allowed after the current status. You may create some
userforms to overcome this
- Difficult reporting if you want something like
-> opprotunities per customer
-> opportunities per customer group
-> etc.
- audit trails are difficult
- Assignment/task creation
- One MAJOR problem: Concurrent usage of this 'application'. Though you
can share a workbook I wouldn't recommend it. Have seen to much
corrupted Excel files after using this 'feature'. Excel is NOT a
multi-user application

------

As you can see, you can do a lot with Excel but it has its drawbacks.
As you didn't provide much detail about the scale of your application
(how many opprotunities, how many people are using this sheet, etc.)
difficult to say if Excel is sufficient for you. ut maybe the above
give you some starting point information. Though you should google for
some (maybe free) CRM applications. You don't have to select
applications like mySAP CRM (beware), Siebel, Peoplesoft, etc. :-)
 
Thank you so much for putting such a detailed answer together for me!

As the process is purely for my own use, I am going to attempt t
develop the Excel option using the details you have offered. I wil
update you on (hopeful) completion of the project.

Thank you again!

Craigi
 
Back
Top