Real Time reporting as spread grows

R

Roger Mount

My department uses an Excel spreadsheet to capture daily Request Fo
Quotes (RFQ) from Customers.
Each RFQ entry requires Customer Name, Customer RFQ Number, Dat
Received, Date Completed, Quantity, Unit Price, Total Dollars, an
other fields.
Each month our Department provides management with a report tha
summarizes the number of bids, days in Estimating, Bids valued ove
$100,000, the average response time in days, and other data.
Is there a way to formulate a running summary of each customer entr
that grows with each new entry? I currently apply a number of "If" an
"Count" formulas for the known range of bids from the beginning to th
end of each month. I would like to capture the same thing automaticall
as the database grows entry by entry so that at the end of the mont
the report is automatically done.
As an example...
#---Cust.------Date in----Date out----Qty---Price-----Total-----Days
1----ABC Co---11/1/03---11/5/03-----100-----$5.00---$500-----4
2----XYZ Co---11/1/03---11/6/03-----1000----$2.00---$2000----5
3----2-------------------------------------------------$2500---4.5
Where line 3 automatically summarizes the data above it. When Line 3 i
overwritten then Line 4 automatically summarizes the data above it
(Days is a formula used for each line entry)
 
H

Henry

Roger,

First thing that comes to mind is NOT to overwrite Row 3 but to insert a new
row 3 to put your new data into. Then row 3 becomes row 4 and all your
formulae are left intact.

You'll have to change your formulae to reflect the new range using an Offset
from the current row or EndUp to find the last row of data that you need to
look at.

HTH
Henry
 
B

brianwa

could you place subtotals accross the top of the report?
ie Insert a new row A.
Select a range of say A3:A2000.
This way you could just enter the data and it will be automaticall
calculated withour having to insert new rows in the body of you
report
 
R

Roger Mount

Brian,
It worked great. Thanks

*could you place subtotals accross the top of the report?
ie Insert a new row A.
Select a range of say A3:A2000.
This way you could just enter the data and it will be automaticall
calculated withour having to insert new rows in the body of you
report.
Click to expand...
 
B

brianwa

My pleasure, I'm glad I could help.
Make sure you freeze the panes just below your header data. That wa
the sub totals are always visable.
B
 
Top