Using numbers to track billing units

Hi LuckyLine,

I am confident that Numbers can do this. A data entry table could use the LOOKUP or SUMIF or COUNTIF functions to search another table (the database). More explanation of what you want will help with a solution 🙂. Perhaps you could reply with a small example of the database table and the entry table.

Regards,

Ian.

LL,

Mock up what you want your log to look like and what you want your report to look like, post screen shots and we'll help you with the programming.

Jerry

Hi LL,

This may get you started.

Your database may look something like this (but much longer 😉)

Your Daily Record Input could be like this:

Input Location, Code and Times done.

D2 contains this formula (and Fill Down)

=VLOOKUP($B2,Database::$A$1:$C$5,2,FALSE)

That will lookup the code in the Database and enter the Description.

E2 contains this formula (and Fill Down)

=VLOOKUP($B2,Database::$A$1:$C$5,3,FALSE)

That will lookup the code in the Database and enter the Units

F2 contains this formula (and Fill Down)

=C2×E2

Select the Daily Record Input table, Copy and then click on a blank place on the canvas. Menu > Edit > Paste Formula Results to get a new table with values, not formulas.

Your Sheet now looks like this:

Because the new table (Daily Record Input-1) contains values instead of formulas, you can safely sort it without messing up the formulas. Sorted by Location (Column A) to start creating your invoices (stay tuned).

Are we there yet?

Regards,

Ian.

Hi LL,

would it be possible to make it on different sheets to de-clutter it?

Yes. Select the Database table and Edit > Menu > Cut (command x).

Add another sheet and Edit > Menu > Paste (command v)

The formulas will automatically adjust. The database is now 'hidden'. You can do that with any table.

For your next trick, while the Database table is selected, Menu > Arrange > Lock will stop accidental changes. When you need to edit the Database table, click on it and Menu > Arrange > Unlock

A further refinement that I was working on was to make the Daily Record Input into two tables.

'Location', 'Code' and 'Times done' to be in one table (unlocked) and 'Description', 'Units' and 'Units Worth' in another table (locked, to protect the formulas from accidental overwritting). Possible, but maybe a future refinement.

it dosnt need to be sorted by locations just all like things added up.

I am thinking SUMIF on split bolt (or Gizmos) and number of times. Doable, but it is now 1 am in Australia. Zzzzz. Will have a go tomorrow 🙂.

Regards,

Ian.

Hi LL,

i need is a table that combines it all, on the last table it dosnt need to be sorted by locations just all like things added up.

Rather than reinvent the wheel, a solution by Barry in this thread might help you. Call back if you need help with this.

Re: inventorylist - checkbox - appear in different sheet ..

Thank for the green tick and your kind feedback.

Regards,

Ian

Hi again, LL,

On second thoughts, that solution will require more work. Each item appears just once on the main (order) table.

Please show how you want your Daily Record table to look.

Regards,

Ian

HI Ian.

I think The linked solution provides a good stepping off place for what's needed here. The table there depends on a column of checkboxes to create an index. Here the index could be created from a count of the item names to mark the first appearance of each on the daily log. As the item numbers are actual numbers, they can be used as the index values, and doing so will enable listing them in order by item number on the summary table usng SMALL to retrieve them in order). Once on the list, SUMIF will calculate the number of times each task was done that day.

Here's an example, using the Daily Record Input table as designed by Ian, and a new Summary table.

Columns A, B and C of Daily Record are filled by the user. Columns D and E are flled from the Database table (see Ian's earlier post) using one of the Lookup functions. Column E is calculated from the values in columns C and E, as described in Ian's earlier post.

Column G (Index) creates an index, using a formula to mark mark the first appearance of each item code in column B of the Daily Report table with the item number. All other rows in this column are filled with a number larger than any of the items numbers.

G2, and filled down: =IF(COUNTIF($B$2:B2,B)=1,B,999999)

Column G is needed for the operation of the Summary table, but is not needed by the user. This column may be hidden.

The summary table contains no entered data. Header labels for each column, and the Total label in the Footer row are the only items entered directly. The rest is filled by a set of formulas. All are entered in row 2 of their respective columns, and are filled down from there to the end of the body of the table.

A2: =IF(ROW()-1>COUNTIF(Daily Record Input :: G,"<999999"),"",SMALL(Daily Record Input :: G,ROW()-1))

This collects the item numbers for the tasks recorded on the daily record. SMALL is used to order these numbers from smallest to largest. The IF part is a switch that shuts off the calculation when the last item number has been copied to this table.

All of the following use IF(LEN(A) to check for content in column A of Summary, and prevent further calculation in none is found.

B2: =IF(LEN(A)<1,"",SUMIF(Daily Record Input :: B,A,Daily Record Input :: C))

If column A is filled on this row, this calculates the number of times the task coded in this row was performed this day.

C2: =IF(LEN(A)<1,"",LOOKUP(A,Daily Record Input :: G,Daily Record Input :: D))

D2: =IF(LEN(A)<1,"",LOOKUP(A,Daily Record Input :: G,Daily Record Input :: E))

These lookup the description and unit value associated with the task coded in column A. They are pointed at the Daily Record table as I did not construct a copy of the Database table. In practice, I would point this formula to the Database table where the original data is stored.

E2: =IF(LEN(A)<1,"",B*D)

This does the same calculation as the similar formula on the Daily Record table, but uses the total number of times each task was performed.

E11: =SUM(E)

Row 11 is a Footer row, and is excluded from calculations specifying a 'whole column' reference. The formula calculates the total number of units listed in column E.

Regards,

Barry