Entering New Weekly Data causes Summary Page to Update..How?

[DrSues02]

Here is the situation:

I have three sheets in my excel file. Summary, Company A, and Company
B.

Each week, I need to add the sales figures for Company A and Company B
on their respective sheets. The sheets would be set up with Week 1 in
Column A and Sales # in Column B. When a new week arrives, I enter the
new sales figures under Week 2.

The summary page lists Company A and Company B and their respective
sales figures with a total at the bottom.

I would like the Summary page to automatically display the Sales
Figures for the last week that I have entered directly on each
Company's page.

For example,

Company A
Week 1
11,000

This would be displayed on the company's sheet and also on the Summary
sheet.

When I add the Week 2 sales figure under this on the company's sheet, I
want the Summary page to display this updated total instead.

Any ideas?

DrSues02

 

[DrSues02]

No ideas? It seems like this wouldn't be too hard to implement I jus
don't know how to do it

 

[Teodomiro]

If I understand the problem correctly, in addition to any othe
summations on the Summary sheet, you want the most recent week's sale
listed.

Here's the solution I came up with. It is clumsier than I like
hopefully someone will be along to clean it up.

In the Company A sheet, we have something like this:

Sales
Week 1 11000
Week 2 15000
Week 3 22000

In the Summary sheet, I put "Week 3" in cell A1, and in cell A2 put th
following formula:

=SUMPRODUCT(('Company A'!A2:A100='Summary'!A1)*('Company A'!B2:B100))

It is clunky, in that you have to manually enter "Week 3", or whateve
week, in cell A1 to get the result. Hopefully, though, this will ge
us started toward a solution