Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Any microsoft excel (for Mac) experts out there?

S

snowydog

macrumors 6502
Original poster
Aug 25, 2011
315
1
Hi all

Looking a bit of advice ...

I have an excel workbook that I call my task and client management.

I have about 40 clients at the minute who I provide monthly consultancy services to.

I have the separate sheet tabs along the bottom with the following titles
  • Client Details
  • Task Managment
  • Funding Deadlines
  • Projects

In my task management I have a number of fields that I want to include, however here's where I'm looking advice. I have a client field where I assign a client to a specific task I have to complete so I know who the task is for ......I know I can filter the client field to show a list of tasks for my specific client but I'm wondering if it would be worthwhile duplicating the task into a client task table in a separate sheet

Basically I want all my tasks in one central place so I can sort based on priorities, status, deadline etc ... But I'd also like to have possibly a client area that has a table with all their tasks and then any notes (but not tasks) for that client

That probably sounds complicated but if anyone understands it and has any advice on the best way to manage this, I'd really appreciate it...
(I've tried a bunch of productivity apps but I like being able to customise my tables as well as design the interface)

Thanks guys
:)
 
J

JAT

macrumors 603
Dec 31, 2001
6,473
124
Mpls, MN
Hmm. Let's see if I follow. You have one database that contains all of the data you have. But you want to show it sorted/filtered for different sections (client, task, etc). There are many ways to do this, actually. The simplest is to use Autofilter on your data. Learn how to use the system and you can push/pull your data around very quickly and effectively. Next easiest is actually pivot tables, although the very mention of the name tends to scare people. ;)

I always look at or plan the end result first. That is, what do you want it to look like when you are done. If you just want to see it yourself quickly while using the file, the Autofilter is probably best. But if you want to format a nice printable report (or many reports), then there are other means with lookups or advanced filters.

I should ask. How big is the file and database?
 
Last edited:
S

snowydog

macrumors 6502
Original poster
Aug 25, 2011
315
1
JAT said:
Hmm. Let's see if I follow. You have one database that contains all of the data you have. But you want to show it sorted/filtered for different sections (client, task, etc). There are many ways to do this, actually. The simplest is to use Autofilter on your data. Learn how to use the system and you can push/pull your data around very quickly and effectively. Next easiest is actually pivot tables, although the very mention of the name tends to scare people. ;)

I always look at or plan the end result first. That is, what do you want it to look like when you are done. If you just want to see it yourself quickly while using the file, the Autofilter is probably best. But if you want to format a nice printable report (or many reports), then there are other means with lookups or advanced filters.

I should ask. How big is the file and database?
Click to expand...


Hi .. thanks for your reply.

Yeh that's right - one database (or workbook) with all the data I need (tasks, clients, funding programmes etc) ... and yeh I'd like to sort/filter it depending on the data I want.

Like you say, I can use sort/filter for myself pretty easily, but my long term plan (the end result as you say) is to have it nicely formatted/designed so I can not only print out the tables/reports .. but also roll out the workbook to the members in my team and then again to the colleagues within the whole company (we have around 21 members of staff) ...

You're right - pivot tables do sound a tad scary, I remember trying to understand them and wasn't doing very well. Definitely something that I need to pay a little more attention to.

I've already added a few buttons to my task management form that will show/hide completed tasks, sort tasks by priority, status or date ....

I'd like to duplicate the completed tasks into the relevant client tables that I'm going to set up .. so for example...I'd have one table for Joe Bloggs , with all the tasks relating to his account within in. Then on my main dashboard page that I am going to develop, I will have a drop down list with my clients in it that I can click on and it will take me to that client table ...

I'm quite lucky that my dad actually knows how to code in excel and he's helped me quite a bit, but he's not got much time to sit and go through these with me and so I wanted to go off and try and learn things myself and then only ask him if I am stuck with something. He sat with me last night for around 3 hours :eek: helping me with my buttons and other bits and pieces..
 
J

JAT

macrumors 603
Dec 31, 2001
6,473
124
Mpls, MN
Well, if you've got good coding help you can do all sorts of things. Although you'll have to get your other personnel to turn on macro capability. It's blocked by the default security setup. (at least, in Windows versions, you have 2011, I presume) Here's a few directions to go...

  • For transferring an unknown number of lines of data, use an Advanced Filter. You need a trigger for this, so a button or a timing action is useful in a macro.
  • If you want to pull one data item at a time, the first place to look is the VLOOKUP function.
  • If you want a total of something like sales by client and by product line, use SUMIFS.
  • If your file is very large, the DSUM (and other Dxxxx functions) are actually much faster to calculate. They are almost like a series of Advanced Filters, and so require a bunch of setup and understanding, but then they are fast, fast, fast.

I find the best is to make your labels on your final report exactly match those in your data. So, if you have Task 1, Task 2, etc., make sure they can match exactly. You probably have to start with the final report, since that has to look a certain way. When they match, it makes lookup functions easier to use, you can reference the column or row header in your report, making it easier to write one formula that can be used in many cells in the report.

It's kinda hard to explain without looking at a spreadsheet together.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom