combining lists
- barry
- FISO Knight
- Posts: 18877
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
combining lists
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!
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
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: combining lists
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.
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
- FISO Knight
- Posts: 18877
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: combining lists
the former mate, doing macros will be beyond me I think, so if there is a simpler way, that would be cool.Surprised wrote: 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?
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: combining lists
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
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
- Grumpy Old Man
- Posts: 3639
- Joined: 24 Jul 2009, 10:54
- Location: Poster formerly known as SOBD
- FS Record: WC2010 FISODAS Champion!!
- FPL:
Re: combining lists
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.
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
- FISO Knight
- Posts: 18877
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: combining lists
liking this move, will give it a wee test run to see if it suits....SOBD wrote: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.
- Mystery
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
combining lists
I haven't tried this but isn't there a "consolidation" wizard that was designed to create a whole year view like this?
-
- Dumbledore
- Posts: 7976
- Joined: 17 Aug 2006, 21:24
- FS Record: FPL: Not as good as it was, but still very respectable.
- FPL:
Re: combining lists
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
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
combining lists
+1
Good advice. Much much easier to store every sale line-by-line database stylee then arrange the presentation separately.
Good advice. Much much easier to store every sale line-by-line database stylee then arrange the presentation separately.
-
- Dumbledore
- Posts: 7607
- Joined: 13 Oct 2005, 18:26
- Location: In hiding
- FPL:
Re: combining lists
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.
View Latest: 1 Day View Your posts