software

Hi people,

I have a workbook that is organized as follows:

First sheet: quot;Raw Dataquot;
A series of timesheet entries, showing worker, date, number of hours worked,
and a job number. Row 1 is a header row, rows 2 through 499 are data rows,
and row 500 is for totals. This sheet has a lot of individual lines of data,
with many instances of any given job number, each with one or more
associated billable hours.

Second Sheet: quot;Pivot Tablequot;
On this sheet, Job Number is dragged into the left column, and Total
Billable Hours is dragged into the data area. The pivot table is sorted by
job number, in ascending order. As a result, this sheet has one row per job
number, and reports all associated billable hours for that job number for
all 498 lines of data.

Third Sheet: quot;Invoicequot;
The invoice tab of the workbook reports on each job number only once, just
like the pivot table, but it is a formatted document, with other information
stuck into it besides just the job number and the associated billable hours.
Therefore, those two elements occur every three lines on the invoice,
instead of on every single line, as is the case with the pivot table.
Moreover, the invoice has extra formatting at each page end, where even more
lines are skipped before another entry referring to the next line of the
pivot table is required.

The most clever idea I have been able to come up with for linking my invoice
tab to my pivot table has been to manually type the formula link into each
cell in the invoice, line by excruciating line, or in the case of the
invoice, third line by excruciating third line, with more lines than that
skipped at the end of each page. There are twelve such entries per page for
job number, and 12 associated entries for billable hours, or twenty four
manually typed formula entries per page. The invoice template goes on for 30
pages, so I think you can see why I would prefer a more automated means of
copying the links into the associated cells.

I have tried to use the autofill method of copying formulas, but I must be
doing something wrong. Can any of you suggest a method of entering these
formulas that involves entering just a couple of link formulas on each page,
and then autofilling the rest?

Spiff
Hi people,

My original post had a lot of words in it, but it really boils down to a
question of how to populate a column with links in nonadjacent rows when the
source data are in adjacent rows.

We can model the same problem by imagining that we have a simple
spreadsheet, one with five values in column A, in rows 1 through 5, say for
instance, 10, 20, 30, 40 and 50.

Now imagine that we want the same exact numbers reported in column B of the
same worksheet, but we want them on lines 3, 6, 9, 12 and 15. How do we copy
the links, so as to avoid typing each one in?

Spiff