View Full Version : Excel question
08-02-2005, 07:42 AM
Hi everyone! I am tinkering with using an Excel spreadsheet to hold all of my next actions, and I have a quick (and probably easy) question.
I have my NA's entered with the context in one column and the NA itself in the next column. I'd like to display a list that suppresses the duplicate contexts so that it's less visually overwhelming. So the first NA in that context would list @phone, but the next 20 would just list the NA, and when the context changes it would list the next one once and so forth.
Is this possible? For some reason I can't seem to figure it out.
08-02-2005, 08:05 AM
You could use Excel's "Conditional Formatting" to format your text color to be the same color as the cells' background color so that the text doesn't show up visually.
Best Regards.....Bill Kratz
08-03-2005, 04:55 AM
the contexts not in use then trying using subtotals. That way you could count the number of NAs in any one context and also hide the ones not in use.
You would either need to add additional NAs into the context or add them to the bottom. Then you would need to remove totals, sort and then reapply totals.
I feel as though there should be some more elegant way but this is the simplest I can think of.
08-03-2005, 07:33 AM
I agree that using the subtotals feature (under the "data" menu) will do what you want. It also gives you ability to expand & contract each context visually on the screen.
08-03-2005, 09:42 AM
If you have each NA as a separate row in the sheet, you can use the Pivot Table reporting tools to generate various reports sorted by context, due date, etc. The nice thing is that they are redrawn automatically as new items are added, so you can keep the input a jumbled mess, and always have the output clean (and formatted for Daytimer forms or index cardsif you use them). This also might avoid the usual problem of electronic tools in that the tool leads you to process at the time you collect.
Unfortunately the mechanics are difficult to show on a board. I suggest you make a backup of your item, then try playing the tool's wizard (under Data) and help to build something that works for you.
08-03-2005, 01:41 PM
But you'll want to use excel 2003. MSFT made a change from 2000 to 2003 that will list all items in your columns or rows without a data field. This allows for you to just
1. put your cursor in your proper list
2. choose pivot table from the data menu
3. choose finish in the wizard (you hardly ever need to change the options.)
4. drag your Context field and then your Next action field (below that) into the Row fields area. This should nicely summarize by context.
The only 'data' option might be to have some time estimates or something.
Certainly a project field would be nice, too, and then you could switch around the fields to get different layouts. You can doubleclick the field names and choose "Subtotals: none" for a cleaner look. When you print, just leave out the data area.