Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
Spaceman Spiff
 
Posts: n/a
Default Linking to a pivot table from a formatted invoice

Hi people,

I have a workbook that is organized as follows:

First sheet: "Raw Data"
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: "Pivot Table"
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: "Invoice"
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


  #2   Report Post  
Posted to microsoft.public.excel.links
Spaceman Spiff
 
Posts: n/a
Default Linking to a pivot table from a formatted invoice

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pivot table rmsterling Excel Discussion (Misc queries) 5 November 14th 05 05:40 PM
pivot table sort entries that don't yet appear in table rachael Excel Discussion (Misc queries) 11 September 19th 05 11:29 PM
Pivot Table - Multiple consolidation Range tengreen Excel Worksheet Functions 1 July 1st 05 07:18 PM
pivot table YingRui Oliviero Excel Discussion (Misc queries) 1 April 12th 05 12:57 PM
Removing errors from a Pivot table Mighty Magpie Excel Discussion (Misc queries) 2 February 3rd 05 04:15 PM


All times are GMT +1. The time now is 12:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"