"Pleading paper" in Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to print Excel data so that it looks like it was printed on
"pleading paper" (this term will be well-known to legal-types. It mostly
involves having an incrementing line number appear at the left edge of the
paper, on every other line. There also has to be a double line at the right
edge of the line-number cells, but I know how to accomplish that part!) The
line numbers need to restart with every new page, and this is where I am
running into problems.

For each worksheet in a book, I want the first 9 rows to repeat at the top
of each page. I figured out how to do that (File/Page Setup/Sheet...), and I
also repeated the first (leftmost) column on every page. I manually formatted
the first 9 rows with "pleading paper" number information as well. Hoping to
use that as a starting point, I tried to use a formula like this in the first
column of the remaining (data) rows:

=IF(CELL("contents",A9)="","",A9+1)

.... attempting to use the pattern set up by the first nine rows to number
the rest of each page. This worked fine for the first page, but it just kept
on incrementing the numbers on each successive page. Obviously, the process
of inserting the "9 rows" takes place at print time, and so this formula
fails. I tried variations on the theme of the formula above before realizing
this.

I figure I'm not the first person in the world to need to do this; but all
my searches so far have been fruitless. Of course, MS Word has a Pleading
Wizard that automates this whole process; and I suppose I COULD import all
the Excel data into Word, and let Word format it into Pleading form. However,
I need to also submit the data in Excel to show other calculations, and it
would just be much more convenient to just keep everything in Excel. I would
be willing to consider accomplishing this using a template or macro, as well
as a formulaic solution.

Thanks,
GB
 
How about printing blank "pleading" formatted pages in Word, and then
*reuse* the paper to print the XL portion?
 
Ragdyer said:
How about printing blank "pleading" formatted pages in Word, and then
*reuse* the paper to print the XL portion?
Click to expand...
....

Some printers' paper feeds get fouled when trying to use sheets of paper
that already have ink on them. That and it can be a PITA to get printout
rows aligned correctly on preprinted forms.
 
geebee said:
on incrementing the numbers on each successive page. Obviously, the process
of inserting the "9 rows" takes place at print time, and so this formula
fails. I tried variations on the theme of the formula above before realizing
this.
Click to expand...

How many lines on a page?
I figure I'm not the first person in the world to need to do this; . . .
Click to expand...

Don't be too sure. Simple enough to add tables to Word documents, so
difficult to see the sense of doing any of this in Excel rather than
entirely in Word.
 
OP stated he preferred not to use that, I suggested he use it to print
"form" and re-use to print XL data, Harlan said it was bad idea, so I guess
that everybody sort of knew about it.<vbg>
 
RagDyeR wrote...
OP stated he preferred not to use that, . . .
Click to expand...

There are occasions on which OPs need to be told to rethink their
assumptions/preconceived notions. This is one of those occasions.
 
Actually, not so much! I didn't go into great detail about WHY I need to keep
the data in Excel, because I didn't think it was relevant to the Forum. The
fact is, I do. This is a court-required accounting, and they (the court
investigator) needs to review the calculations in the raw Excel data. He
isn't all that technically advanced, and just the IDEA of sending him a Word
document with links to an Excel spreadsheet... [shudder].

Apparently, attorneys are asked to do this (submit data in Excel) a lot. I
was hoping someone would have already worked out a solution for it. It may
well be that most attorneys do what RagDyer suggested: printing out
pleading-paper "forms" using Word, and then printing Excel data over top.
Harlan is right about issues with printer jams and non-alignment with
preprinted data ("registration errors"); however, I regularly see briefs come
out of attorney's offices which were clearly done in Word, but the text
doesn't line up with the line numbers because somebody switched a font or
font size!! So apparently the courts are somewhat tolerant of such errors. It
would be interesting to see if there was a correlation between papers filed
this way and cases lost....

But back to the problem: I'm very sure that there is a way to detect a Page
Break through Visual Basic. It's probably some facet of the Page object, but
I'm not enough of a programmer to figure it out (or at least, not easily!)
Assuming that Page Breaks CAN be detected through VB, presumably one could
write a Macro to fill the first column of a Worksheet with appropriate
numbers. Any of you wikked programmers want to take a shot at it?? I can pay
(not too much) for a solution...

Thanks,
GB
 
geebee said:
The fact is, I do. This is a court-required accounting, and they (the
court investigator) needs to review the calculations in the raw Excel
data. He isn't all that technically advanced, and just the IDEA of
sending him a Word document with links to an Excel spreadsheet...
Click to expand...

O/T asking why accounting information would need to appear in the form of a
legal pleading rather than as an unformatted appendix to a legal pleading.

However, I wasn't suggesting links to an Excel worksheet in a Word document.
I was suggesting using Word tables instead of Excel. If the calculations in
question are accounting-based, the most sophisticated calculations would be
discounting, no?

Apparently, attorneys are asked to do this (submit data in Excel) a lot.
Click to expand...
....

Don't you mean data _from_ Excel?

Isn't this one of the things secretaries get paid to do?

Now to try to be helpful. If you're filing hardcopy with courts, the courts
aren't going to care whether you printed from Word, Excel or The Ambulance
Chaser Suite. Print the Excel workbooks to *TEXT* files or save them as text
files, open those text files in Word's legal pleading template, print from
there as hardcopy. I suspect this is how other firms do it. No good purpose
served trying to reinvent the wheel.
 
Harlan said:
O/T asking why accounting information would need to appear in the form of a
legal pleading rather than as an unformatted appendix to a legal pleading.

However, I wasn't suggesting links to an Excel worksheet in a Word document.
I was suggesting using Word tables instead of Excel. If the calculations in
question are accounting-based, the most sophisticated calculations would be
discounting, no?




...

Don't you mean data _from_ Excel?

Isn't this one of the things secretaries get paid to do?

Now to try to be helpful. If you're filing hardcopy with courts, the courts
aren't going to care whether you printed from Word, Excel or The Ambulance
Chaser Suite. Print the Excel workbooks to *TEXT* files or save them as text
files, open those text files in Word's legal pleading template, print from
there as hardcopy. I suspect this is how other firms do it. No good purpose
served trying to reinvent the wheel.
Click to expand...
Recognizing that you do indeed mean *in* Excel (for the convenience of
the court investigator), and that like many attorneys, particularly
those with small practices, you don't have or don't always use a
secretary, perhaps you could just make an Excel template of blank
pleading paper numbered from 1 to, I assume, 26. Then you can enter
onto the template the first 9 rows of data and, if it's usually the same
attorney information and court, save that as a second template to be
used for first pages of pleadings; or have the first 9 rows entered on
an Excel formatted 9-row template and simply copy-paste that data to the
first page of the 26-line pleading paper.

On your first-page template you could even include formatting for the
parties and caption, though that's a little trickier; but handy once
you've worked it out.

Another approach, if the court's rules do permit an accounting appendix
on other than pleading paper, is to do the pleading paper in word and
the appendix in Excel and provide just the appendix in electronic form
to the court investigator.

Good luck,
Alan Beban
 
Alan Beban said:
Recognizing that you do indeed mean *in* Excel (for the convenience of
the court investigator), . . .
Click to expand...
....

If you're correct, then the goal would be to make Excel workbooks appear as
if they're written in pleading format on screen. There are times (OK, darn
near all the time) I'm grateful I never wanted to be a lawyer!
 
OK, let me see if I understand this. You have to print the XL document
with a particular line numbering requirement. You also have to send
the actual XL document for verification purposes.

So, bottom line is that the line numbers must be in the XL document,
not just on the printed paper.

Correct? If so, the code below does what you want. Were you serious
about paying for it? Obviously, you don't have to since the code is
below. {g} But, if you were, how does $25 sound? Visit
http://www.tushar-mehta.com/paypal/ and I will use it to increase my
contribution to one of the causes I support.

Option Explicit

Sub testIt()
Const TopRows As Integer = 9
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$" & CStr(TopRows)
.PrintTitleColumns = "$A:$A"
MsgBox .PrintTitleRows & vbNewLine _
& .PrintTitleColumns
End With
With ActiveSheet
.UsedRange.Cells(.UsedRange.Cells.Count).Activate
'XL has some strange problem with the pagebreaks _
collection if the entire sheet hasn't been viewed
.Columns("A").ClearContents
Range(.Cells(1, 1), .Cells(TopRows, 1)).Formula = "=ROW()"
.Cells(TopRows + 1, 1).Value = TopRows + 1

Dim allHPageBreaks As HPageBreaks, _
aHPageBreak As HPageBreak, i As Long
Set allHPageBreaks = ActiveSheet.HPageBreaks
For i = 1 To allHPageBreaks.Count
Set aHPageBreak = allHPageBreaks(i)
With aHPageBreak.Location
MsgBox .Address
.Value = TopRows + 1
End With
Next i

With Application.Intersect(.UsedRange, .Columns("A"))
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"
.Copy
.PasteSpecial xlPasteValues
End With
End With
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Actually, not so much! I didn't go into great detail about WHY I need to keep
the data in Excel, because I didn't think it was relevant to the Forum. The
fact is, I do. This is a court-required accounting, and they (the court
investigator) needs to review the calculations in the raw Excel data. He
isn't all that technically advanced, and just the IDEA of sending him a Word
document with links to an Excel spreadsheet... [shudder].

Apparently, attorneys are asked to do this (submit data in Excel) a lot. I
was hoping someone would have already worked out a solution for it. It may
well be that most attorneys do what RagDyer suggested: printing out
pleading-paper "forms" using Word, and then printing Excel data over top.
Harlan is right about issues with printer jams and non-alignment with
preprinted data ("registration errors"); however, I regularly see briefs come
out of attorney's offices which were clearly done in Word, but the text
doesn't line up with the line numbers because somebody switched a font or
font size!! So apparently the courts are somewhat tolerant of such errors. It
would be interesting to see if there was a correlation between papers filed
this way and cases lost....

But back to the problem: I'm very sure that there is a way to detect a Page
Break through Visual Basic. It's probably some facet of the Page object, but
I'm not enough of a programmer to figure it out (or at least, not easily!)
Assuming that Page Breaks CAN be detected through VB, presumably one could
write a Macro to fill the first column of a Worksheet with appropriate
numbers. Any of you wikked programmers want to take a shot at it?? I can pay
(not too much) for a solution...

Thanks,
GB

Harlan Grove said:
RagDyeR wrote...

There are occasions on which OPs need to be told to rethink their
assumptions/preconceived notions. This is one of those occasions.
Click to expand...
Click to expand...
 
Tushar Mehta wrote...
OK, let me see if I understand this. You have to print the XL document
with a particular line numbering requirement. You also have to send
the actual XL document for verification purposes.

So, bottom line is that the line numbers must be in the XL document,
not just on the printed paper.
Click to expand...
....

Your macro doesn't do what the OP asked. It just creates sequential
line numbers. Why a macro is needed to do this rather than just using
=ROW() in all cells in column A is a mystery to me.

Reread the OP's specs and do a little bit of research. All pages in a
legal pleading start with row number 1 and end with either 25 or 28
depending on paper size. Here's a specimen.

http://www.azd.uscourts.gov/azd/cou...c182007256e84008319d1/$FILE/pleadingpaper.pdf

(or http://makeashorterlink.com/?N3E1428CA ).

If numbers really are supposed to appear every other line, and ignoring
blank lines above the top line and below the bottom line in the body of
the page, then column A needs a 1 in row 1, a 2 in row 3, etc. The top
9 rows repeat on each page, so line numbers 1 through 5 would repeat
from rows 1, 3, 5, 7 and 9. Row 11 would show line number 6, row 13
line number 7, etc. through row 55 showing line number 28. Then the
line number would reset to 6 in row 57. If all this holds, then this
could be done with simple worksheet formulas. Enter the following
formula in A11.

A11:
=IF(MOD(ROW(),2),MOD(ROW()-11,2*(28-5))/2+6,"")

Fill A11 down as far as needed. Then play with the page setup to ensure
that pages break 2 rows after the rows containing line number 28. If
the particular jurisdiction in question uses line numbers only through
25, replace the 28 in the formula above with 25.


If it's not necessary for the Excel files to appear like this on
screen, it'd be REALLY FOOLISH to screw them up with this sort of
formatting. If they only need to appear as pleadings in hardcopy, I'll
repeat that it'd be better to print them as text files from Excel, then
import them into Word's legal pleading template and print from there.
 
Your macro doesn't do what the OP asked. It just creates sequential
line numbers. Why a macro is needed to do this rather than just using
Click to expand...

And, what do you think the following does?

For i = 1 To allHPageBreaks.Count
Set aHPageBreak = allHPageBreaks(i)
With aHPageBreak.Location
MsgBox .Address
.Value = TopRows + 1
End With
Next i
Reread the OP's specs and do a little bit of research. All pages in a
legal pleading start with row number 1 and end with either 25 or 28
depending on paper size. Here's a specimen.
Click to expand...

It is rather arrogant of you to presume I didn't research what a
pleading document is before coming up with my solution.

Get off your high horse, Harlan. You are not the only person on this
planet with a brain. 1/2 a brain maybe, a complete one, no way.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Just a question.

I can understand how MSWord works with a pleading document. It counts lines per
page.

But in excel, you have rows and columns. Wouldn't you need a counter for both
rows and columns?

This may not work for you (or your bosses or the court), but I used it when I
printed documents for meetings.

File|page setup|Sheet tab
Check "row and column headings"

If you need numbers for the columns, you can change that via:
tools|options|general tab|check R1C1 reference style box.

Note that your formulas will change to that R1C1 format, too.
 
Tushar said:
(e-mail address removed) says...

And, what do you think the following does?

For i = 1 To allHPageBreaks.Count
Set aHPageBreak = allHPageBreaks(i)
With aHPageBreak.Location
MsgBox .Address
.Value = TopRows + 1
End With
Next i
Click to expand...

I did test it, but apparently not thoroughly. It does reset to TopRows
+ 1 after each page break.

That said, if the OP hasn't yet set page breaks, your macro almost
certainly doesn't produce the required line numbers - ending at 28 then
resetting to 10. Adding your macro to a new workbook with factory
default US page settings, A1:A52 get line numbers 1-52, A53:A103 get
line numbers 10-60, A104:A154 get line numbers 10-60, etc. If you
wanted to make this a *USEFUL* macro maybe worth a few bucks ($25.00?
You're smokin' something!), then why not *ENSURE* that line numbers
reset after 28 *AND* insert a page break in the row below where the
line number resets to 10?!
It is rather arrogant of you to presume I didn't research what a
pleading document is before coming up with my solution.
Click to expand...

OK, so you didn't understand it. Fine. You're not (only) careless and
lazy, you're stupid. Or didn't you read the OP's original message, in
which:

'"pleading paper" (this term will be well-known to legal-types. It
mostly
involves having an incrementing line number appear at the left edge of
the
paper, on every other line.'

Emphasis on the phrase 'on every other line'. This is a bit complicated
because parts of such documents may be single spaced (parties to the
suit, attorneys, etc. at the top of the first page, footnotes and
verbatim quotes set off from the main text) while other parts must be
double spaced. For example,

http://www.bcltechnologies.com/document/products/drake/samples/Pleading.pdf

At best uncertain whether spreadsheet exhibits would need to be double
spaced or could be single spaced.

BUT if it's as simple as you believe it to be *AND* line numbers range
from 1 to 28, just use formulas.

A1:
1

A2:
=IF(A1=28,10,A1+1)

and fill A1 down as far as needed.

BTW, *YOU* are the one assuming on screen *MUST* look like hardcopy,
and hardcopy appearance is rigidly specified. *EVERY* hardcopy page
must run from line number 1 to line number 28, so *EVERY* on screen
'page' must also run from line 1 to line 28, so given the top 9
lines/rows frozen, all other lines must run from 10 to 28 with *NO*
pages stoping before line 28 or extending past line 28. Horizontal page
breaks are *IRRELEVANT* or should be set at each row in which the line
number resets to 10 (but not the first instance of line number 10 in
cell A10). So your macro is ill-conceived (rough translation: you wanna
get paid for this dreck?!).
Get off your high horse, Harlan. You are not the only person on this
planet with a brain. 1/2 a brain maybe, a complete one, no way.
Click to expand...

Stand up, Tushar, and take some pressure off your brain.
 
Back
Top