linking (many) multiple cells to one worksheet

  • Thread starter Thread starter kristee
  • Start date Start date
K

kristee

Hi.
I have created (so far) a work book for the sales people in my offic
that shows: how many leads they have received (via mail, internet an
other) vs. how many sales that person has made. The work book is set u
so each month is on a separte work sheet and has its respective totals
with links to another worksheet that links those totals to show ho
each sales person has been doing for the entire year (i will call i
the "total" worksheet). In each month (and on the 'total' worksheet
there is also a row for "House" sales - which is a total of leads/sale
received from people who do not work from our office. Also, whe
salespeople no longer work for us, their information is combined int
the "house" row on the Totals worksheet, but I leave their origina
information in the month that they were working in our office.
The problem I have run into is (and i know there is some simpl
solution i cannot seem to find...):
After so many people have left, i am no longer able add thei
information into the "house" row in the Totals worksheet, as the hous
row will only accept 30 'arguments'. Because the reference cel
changes per person for every month, i currently have links written fo
each indiviual cell i want added into the house row on the total
worksheet. Is there an easier way to combine the long gon
salespersons' information into the Total worksheet house row? Or ho
can I make it so that the cell will accept more than 30 arguments
(thats what the 'pop-up' says when it refuses to let me finish th
equation....)
ps - i took a look at nesting, but because i am not looking to us
averages, i dont think it will work....
I appreciate if any one could help, especially if the instructions ar
in laymans terms.... i am quite new to the program....
thanks
 
kristee,

It's time to throw out your current workbook and start from scratch using a
better design.

It's called a Database, and the optimum use is with Access not Excel.
However, Excel can do databases (just not as well).

Set up your data table on a single sheet to include all the needed
information: where the work was done (in house or out of house), sale date,
year, month, salesman name, amounts, how lead was received, etc. with one
row for each item.

Then you can use a pivot table on the database and extract all that
information without using a single formula.

HTH,
Bernie
MS Excel MVP
 
Back
Top