combining lists

[barry]

helping a friend out with a basic spreadsheet, but feel I (we ) could jazz it up a bit....

it's basically a 12 page (jan to dec) list of month to month dealings with clients, so each page has a list of clients in column A, columns B to E have the month split into 4 periods, then column F totals this to give a monthly list of how many dealings they had with each client, with a total at the bottom, and a rolling total (jan total, feb + jan, march + feb rolling, april + march rolling etc)

I would like to add an annual running total page, but was wondering A - if this was possible, and B - the best way to deal with it. Especially with regard to list of clients, obviously there are a core of clients that will be the same each month, but some months will have a new client being added, I was just wondering how best to have a "live" page that picked up each name from each month (without duplication) so if a new client was added to the March Page, it would be filled into the annual page etc etc and the client that they dealt with every month of the year was added once with figures being totalled, rather than listed 12 times.

let me know if you need more info.


cheers guys!

[Surprised]

It is certainly possible as a surprising number database things can be accomplished in Excel. It may need a macro to automatically populate the annual page though but I'm not totally sure that will be needed.
To clarify.
Say you have client A with 3 dealings in Jan
Then in Feb you have 4 more dealings with client A and 2 dealings with a new client, B

Do you just want the new page to show Client A with 7 dealings and Client B with 2 or do you want the month by month dealings too?

It might be easier to have a separate page with all the client names on and then give each client a code.
You can add to it as you need and use simple formulas on the month pages to look up the client names.

[barry]

To clarify.
Say you have client A with 3 dealings in Jan
Then in Feb you have 4 more dealings with client A and 2 dealings with a new client, B

Do you just want the new page to show Client A with 7 dealings and Client B with 2 or do you want the month by month dealings too?

the former mate, doing macros will be beyond me I think, so if there is a simpler way, that would be cool.

[Surprised]

It can be done although it probably won't all be automatic as you will probably need to add the client or client code into the front page.

I can have a look in the next few days. It would be easier if you could send an example with dummy data but it's not essential

[Rob]

I like to keep things simple where possible, so my first thought is to work backwards from the final month.

Cell A2 in December would link to A2 in Nov, which would link to A2 in Oct, etc back to Jan. Copy the formula down to A100 or whatever's appropriate. The front/summary page cell A2 would link to December cell A2 in the same way.

That way if a new client were to be added to the bottom of the list in March, for example, then it would flow through to April to Dec and to the front page.
This wouldn't cater for some dumbass adding a client twice as they would then appear on the summary twice.

[barry]

I like to keep things simple where possible, so my first thought is to work backwards from the final month.

Cell A2 in December would link to A2 in Nov, which would link to A2 in Oct, etc back to Jan. Copy the formula down to A100 or whatever's appropriate. The front/summary page cell A2 would link to December cell A2 in the same way.

That way if a new client were to be added to the bottom of the list in March, for example, then it would flow through to April to Dec and to the front page.
This wouldn't cater for some dumbass adding a client twice as they would then appear on the summary twice.

liking this move, will give it a wee test run to see if it suits....

[Mystery]

I haven't tried this but isn't there a "consolidation" wizard that was designed to create a whole year view like this?

[hancockjr]

Haven't really followed this but a common mistake, and I think it is happening here, is to store the information in the way you want it displayed. I suspect the answer is to have the "database" in a relatively "raw" format, with one record per entry, including data like month (or date) etc, on one tab. Whatever you then want to see you can do via reports. These may well be best achieved via pivot tables, but there are other options (summary tables, extracting relevant records from the database etc).

[Mystery]

+1

Good advice. Much much easier to store every sale line-by-line database stylee then arrange the presentation separately.

[bspittles]

To keep it in the current format, you could give a name to each of the 12 "totals" cells, then add them up on a separate page. Inserting a row to add a new client would simply move the name reference down at the same time as the total moved.