Making a schedule

Question

Level 1

4 points

Making a schedule

I have been keeping a schedule of projects for my business using numbers 2.9 for a few years. It worked pretty well. When 3.0 came out, It didn't work for me because of the single column sorting thing. But now that they have re-introduced multi-column sorting, I'd like to give the listing a try again in 3.1, but I need a little help..

The way my schedule works is this, there are 3 pages, Orders in, Open Orders, and Invoices (finished orders). I generally would enter an order on the "orders in" page, keeping a list by day, copy them to the Open orders page (which becomes the schedule of things to do), and then, when the project is done, cut from the open orders page to the Invoices page, pretty simple, right?

Here is the rub, Each page, obviously, has a different use and therefor different columns. On the orders in page, we would enter orders, one per row, and have all the columns we needed in each of the three different pages, some even had formulas in them, but hid the ones that weren't needed on that page. In 2.9. when you copied a row and pasted it into a different table, it would paste only the columns that were visible in the original copy, just as 3.1 does now, but if you INSERTED the row, it would paste column A into column A and column B into column B regardless of it's visiblility (which, to me seems to be a no brainer as to how it SHOULD behave, but who am I to say). But now, in 3.1 inserting, now, behaves the same, pasting only the visible columns in, seemingly, random places.

Are there any ideas of how to do this more efficiently?

Posted on Jan 28, 2014 6:02 AM

Reply

Answer 1

Jan 28, 2014 6:42 AM in response to bobbyv33

I would suggest having all orders in the same list and using filters to see the surrent ones. Have a status column with a pop-up to identify "Orders In", "Active", and "Invoice"

Then make a summary page(s) (if necessary) to see groups of orders in those states:

Date	Order Number	Invoice Number	Status	Other informaiton
	O20140001	I20140001	Invoice
	O20140002	I20140002	Invoice
	O20140003		Active
	O20140004		Active
	O20140005		Active
	O20140006		Active
	O20140007		Order In
	O20140008		Order In
	O20140009		Order In

Filtered on Status:

Date	Order Number	Invoice Number	Status	Other informaiton
	O20140003		Active
	O20140004		Active
	O20140005		Active
	O20140006		Active

Filter is set up like this:

Reply

Answer 2

bobbyv33 Author

Level 1

4 points

Jan 28, 2014 7:28 AM in response to Wayne Contello

Different lists serve different purposes. I can't use the same list and print out different versions of it.

Orders in needs to tell me daily totals, averages, who ordered what and when. Summaries as well. Same for invoices. Cerrtainly I can sort and hide and unsort and resort and filter, but that is way too complicated for me to have the lists that i need. In addition, the Orders list and the invoice list start from 0 every month while the open projects list is continually updated.

This is a part of the enter order (invoices looks similar), notice that column B, C and E are hidden.

This is a section of the open orders page. Note that now we see B & C but E, which is used in the invoice page (for the invoice date) is still hidden.

Reply

Answer 3

bobbyv33 Author

Level 1

4 points

Jan 29, 2014 4:48 AM in response to Wayne Contello

BTW, Wayne, Thanks for your suggestion. I think my problem with your suggestion is that I would end up with a never ending list that would just be too huge to handle, unless I am not understanding what you are saying.

Reply

Answer 4

Jan 29, 2014 5:56 AM in response to bobbyv33

Bobby,

Then separate by years in different sheets. The simpler the solution the eaiser it is to maintain.

Reply

Answer 5

bobbyv33 Author

Level 1

4 points

Jan 29, 2014 6:13 AM in response to Wayne Contello

I think that would be more complicated, unless I am missing something. Thanks to cut and paste doing it on three separate sheets is very simple.

enter the order. Copy the entered order and PASTE to open orders. When the order is completed, CUT from open orders and pasted to completed orders. Couldn't be any simpler.

No filtering on and off, no changing of the status (cutting and pasting is easier than changing the status, no?), no complicated formulas based on status for the sumaries, no huge pages.

Am I missing something, Wayne?

Reply

Answer 6

Jan 29, 2014 6:22 AM in response to bobbyv33

I think this is a difference of opinion and me not really understanding your problem. Try showing more context and exaplin your workflow again.

Reply

Answer 7

bobbyv33 Author

Level 1

4 points

Feb 6, 2014 7:16 AM in response to Wayne Contello

I keep three different lists. I keep them SEPARATELY from our bookkeeping system as a check. I run the production so I like to reconcile production with bookkeepping.

List one, orders in. totaled by day. We need to keep averages and statistics daily. totaled and restarted monthly.

List two. Open orders, sorted by due date. Used as a production schedule. Continuous, never restarted.

List three, Finished orders. totaled by day. Once again, needed with daily averages and stats. Totaled and restarted monthly.

Orders are entered, as they come in, on list one. Copied from list one and pasted to list two. When the factory is finished with the order it is CUT from list two and pasted onto list 3. Keeping list two current with ONLY orders to be worked on, List one with a monthly list of orders entered, totaled by day, and list three current, daily, with completed orders. The three lists are kept as sheets in one file.

At the end of every month, I duplicate list three and rename it to the month (ie Jan 14). I then cut all the info from list one and paste it to the last month file for archiveing. I then delete the info in list one and start again (not doing anything to list two other than the normal daily changes).

I came up with this system while studying aviation where the control tower moves tiles from one list to another representing planes in the air.

Once again, thanks for your help.

Reply

Answer 8

SGIII

Level 8

35,336 points

Feb 6, 2014 9:58 AM in response to bobbyv33

Hi bobbyv33,

I can see the advantages of simply copying from one list to another.

I've noticed that you can drag a row from a table to another table. To drag a row click the row number and move the cursor with the mouse button held down (or trackpad equivalent) to "lift" the row out of the table and drop insert it where you want, in that table or another. (The maneuver is little tricky at first but comes with practice.)

Even if the destination table has hidden columns, you get the result you want (with the values in the right cells, including the hidden column--you can unhide it to verify):

But if you hold the option key down while doing this (to copy the row rather than move it) then on my maching the cells in the resulting row in the destination table do not line up correctly. I suspect this is a bug, unless someone else can figure it out.

In any case you do have simple drag and drop (achieving the effect of cut and paste). So that may make Numbers 3.1 suitable for your workflow. The problem is where you need to add a row to another table but leave it in the original table as well.

If you get the same results as I do, perhaps report this to Apple via Numbers > Provide Numbers Feedback in your menu?

SG

Reply

Answer 9

Feb 6, 2014 11:41 AM in response to bobbyv33

Bobby,

I've read the thread and I understand your points and the suggestions that have been offered. If I was doing this for myself, I would have one Master Table with a row (record) for each order or Job. The Master Table would have Date Received, Customer ID, Order Number, Job Description, Quoted Price, Date Production Started, Date Shipped, Date Payment Received(plus other important details). Nothing would ever leave that table unless you were tired of looking at it or if it became difficult to navigate because of its size. At that time I would achieve what I didn't want to be tripping over. (This achieve in addition to a robust daily backup routine on the active file.)

Then I would create three or more distinct tables for listing jobs in queue, jobs in production and jobs shipped awaiting payment, etc. These sub-tables would be customized to their exact business needs for their particular phase of the project. They would pull data automatically from the Master Table based on dates and other status fields in the Master Table.

This is all easy with Numbers, unless your needs are considerably more stringent than what you have described.

Jerry

Reply

Answer 10

SGIII

Level 8

35,336 points

Feb 6, 2014 1:04 PM in response to Jerrold Green1

They would pull data automatically from the Master Table based on dates and other status fields in the Master Table.

This is all easy with Numbers, unless your needs are considerably more stringent that what you have described.

Hi Jerry,

Pulling the data out of a master table automatically and summarizing it is neat, using the techniques you and others have pioneered here. But if you need to be able to sort the sub-tables, and yet still have them pull data from the master table automatically, things seem to get more complicated.

I suppose one way to handle this would be to have sub-tables set up that pull the data automatically. Then every time you need to sort a sub-table, command-c to copy the table (as an object, not just the contents) and Edit > Paste Formula Results to get a new version of the table that you can sort, keeping the old one intact to keep updating from the master table.

Not too bad. But there is something to be said for being able to drag and drop a copy of a row where you want it, leaving the original in a master table for record keeping purposes. The control tower moving tiles from one list to another is a good system, though in this case it would be good to also keep a pile of the tiles in their original placement, which is not as convenient in Numbers 3 as it should be.

SG

Reply

Answer 11

bobbyv33 Author

Level 1

4 points

Feb 7, 2014 5:50 AM in response to SGIII

Dragging the rows works perfectly, thanks. It hides and unhides the colums exactly as I wanted thanks.

A couple of questions though....

do you know of a way to drag the row from one sheet to another? What I ended up doing was copying the whole table to the new sheet and then taking the rows, one at a time into the new table. I will try doing that a while unless we come up with a better solution.

There also seems to be a problem draging the last row (a single row in a table). Dragging with the option key does duplicate the row, but when it is inserted the hidden columns don't adjust so the come out the same as just inserting (that must be a bug, I would think that duplicating and dragging and dragging would behave the same way, I will report it). Adding. a blank line to the bottom of the copied table seems to solve that problem.

My work around was to copy the table from one sheet to the next (or create a table from the rows I needed). and then adjust, manually, the hidden columns. then copy and paste them where they need to be.
d for a few days and see.

I am not sure which is easier. I will try the new metho

Reply

Answer 12

SGIII

Level 8

35,336 points

Feb 7, 2014 11:01 AM in response to bobbyv33

If there is a way to drag a row from a table in one sheet to a table in another sheet, I haven't found it. That is probably a design limitation that will be hard to change.

But the inability to cut and paste a row, or copy and paste a row, while respecting hidden columns in the destination table on another sheet, seems to be a bug.

As does the fact that you can't option drag (i.e. copy) a row from one table to another table on the same sheet and have it respect any hidden columns. As you say, you would think one could do that since it is possible to just drag and drop (i.e. move) with good results even when there are hidden columns.

SG

Reply

Answer 13

Feb 7, 2014 11:25 AM in response to SGIII

SGIII wrote:

They would pull data automatically from the Master Table based on dates and other status fields in the Master Table.

This is all easy with Numbers, unless your needs are considerably more stringent that what you have described.

Hi Jerry,

Pulling the data out of a master table automatically and summarizing it is neat, using the techniques you and others have pioneered here. But if you need to be able to sort the sub-tables, and yet still have them pull data from the master table automatically, things seem to get more complicated.

I suppose one way to handle this would be to have sub-tables set up that pull the data automatically. Then every time you need to sort a sub-table, command-c to copy the table (as an object, not just the contents) and Edit > Paste Formula Results to get a new version of the table that you can sort, keeping the old one intact to keep updating from the master table.

Not too bad. But there is something to be said for being able to drag and drop a copy of a row where you want it, leaving the original in a master table for record keeping purposes. The control tower moving tiles from one list to another is a good system, though in this case it would be good to also keep a pile of the tiles in their original placement, which is not as convenient in Numbers 3 as it should be.

SG

SG,

Please excuse my tardy reply. I guess I'm not the best one to ask about dragging and dropping Rows in Numbers 3. I've comfirmed your observations, but beyond that I don't have much experience with it.

In the context of this thread, I'm not a fan. To me that's too much manual activity, subject to error, whereas once automatic fetching is debugged it should be very reliable. Yet, it seems the OP is pretty set on that approach, so if I can come up with something better along those lines, I'll post back.

Jerry

Reply