P
Pete Petersen
Ok...This might be a SQL question..or i might say, this might be able to be
performed through a query.
As of right now we generate a refreshable spreadsheet from our order entry
system (SQL 2000) for commissions. The columns that we display are as
follows:
Segment Date Sold Customer Invoice Cost Sell Gross Profit
These colums above are pulled from the database and put into excel 2003.
Then we have added the following 2 static Columns:
Gross Margin % Commissions
(Gross Profit/Sell) Gross Profit*.06
Here is the first question:
When I refresh the sheet how can i get the formulas to automatically refresh
as well. As of right now, when I refresh the data, the GM% and Commission
Columns do not fill in...I have to manually copy down the formulas.
Question two(and thank you for reading this far):
I want to create a second sheet with the following information and I don't
know the best way to do it. Right now we have to manually update all of
these fields.
Date of 1st Purchase Segment Customer Jan Feb Mar Apr ...
YTD 2005 Potential 2005 Projections 2004 Sales 2003 Sales 2002
Sales
I would like to know the best way to lay this out..do i use pivot
tables...is there ways to make this sheet have data pulled from multiple
sheets and also add static data. The months would be SUM of all sales for
that month. All of the columns could come from refreshable data sheets
except for the 2005 potential. That would be base off of the Segment. That
column would be as follows.
A = 75,000
B = 25,000
C = 10,000
D = 5,000
E = 1,000
2005 Projections is a static field that is updated by the Rep per customer
based on spikes/trends.
Thank you for any or all your help. Have a great day and happy holidays.
Pete Petersen
performed through a query.
As of right now we generate a refreshable spreadsheet from our order entry
system (SQL 2000) for commissions. The columns that we display are as
follows:
Segment Date Sold Customer Invoice Cost Sell Gross Profit
These colums above are pulled from the database and put into excel 2003.
Then we have added the following 2 static Columns:
Gross Margin % Commissions
(Gross Profit/Sell) Gross Profit*.06
Here is the first question:
When I refresh the sheet how can i get the formulas to automatically refresh
as well. As of right now, when I refresh the data, the GM% and Commission
Columns do not fill in...I have to manually copy down the formulas.
Question two(and thank you for reading this far):
I want to create a second sheet with the following information and I don't
know the best way to do it. Right now we have to manually update all of
these fields.
Date of 1st Purchase Segment Customer Jan Feb Mar Apr ...
YTD 2005 Potential 2005 Projections 2004 Sales 2003 Sales 2002
Sales
I would like to know the best way to lay this out..do i use pivot
tables...is there ways to make this sheet have data pulled from multiple
sheets and also add static data. The months would be SUM of all sales for
that month. All of the columns could come from refreshable data sheets
except for the 2005 potential. That would be base off of the Segment. That
column would be as follows.
A = 75,000
B = 25,000
C = 10,000
D = 5,000
E = 1,000
2005 Projections is a static field that is updated by the Rep per customer
based on spikes/trends.
Thank you for any or all your help. Have a great day and happy holidays.
Pete Petersen