Excel 2010: copying formulae down a column which references another spreadsheet

[pendle666]

Hello

I have a spreadsheet which contains the total sales for each person on a specific day, here is a section:

Branch Totals
[pre]
A B C
6 Date John Brown Michael Green
7 1 £- £-
8 2 £- £-
9 3 £- £1,469.99
10 4 £499.98 £-
11 5 £1,099.99 £-
12 6 £990.00 £-
13 7 £- £-
14 8 £- £599.99
15 9 £39.99 £699.99
16 10 £- £119.99
17 11 £- £-

[/pre]

The time period varies, in this example it's from 5 December to 8 January, the next period is 9 January to 12 February. Although the branch totals run for the calendar month.

Now I have another spreadsheet which contains the commission calculation for the salesperson. This needs to include the data above. Here is a bit of it:

Commission Sheet
[pre]
A B
3 Date Sales
4 05/12/15 1,099.99
5 06/12/15 990.00
6 07/12/15 0.00
7 08/12/15 0.00
8 09/12/15 39.99
9 10/12/15 0.00
10 11/12/15 0.00

[/pre]

Now I have used a formula in Commission Sheet in cell B4 =[branch.xlsx]totals!$B$11 so it's looking at the Branch Totals sheet and the total for that sales person for 5 December. 6 December will be =[branch.xlsx]totals!$B$12 and so on.

So far so good.

But I have several branches and many people to deal with so changing the dates each period can be a bit of a pain. What I would like to do is have something in the cells below B4 which references the next cell after the one above it. So that B5 knows to link to =[branch.xlsx]totals!$B$12 for the 6th December and so on.

Is this possible? Am I asking too much?

thank you in advance

thank you for helping

____________
Pendle

 

[SkipVought]

HI,

"In this example its from 5 December to 8 January"

??? I see days 1 thru 11???

That being said, I'd make the effort to map ALL the data into a PROPER table with...

Actual Date (5/12/2015 etc)
Sales Person
Sales Amount
Branch

This would be an ongoing master table, from which you could generate all kinds of good stuff for your concern such as year to date, comparisons with previous year, month, quarter etc.

I'd probably do this via VBA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

The days 1 to 11 are in a spreadsheet which is calendar monthly, but I don't think there's anything to stop me making that and my commission sheet the same start date, it's only me that uses these particular files, everyone else has their own.


thanks


thank you for helping

____________
Pendle

 

[SkipVought]

I just did not understand the disconnect between your statement and the example.

My suggestion stands: make a PROPER table containing all the data presented in one tabluar format.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]