Transaction Classifications in Checkbook

[mscureman]

I'm trying to modify a checkbook register template so that I can assign
classification codes to each transaction, then have a copy of all
transactions of a given type go onto a separate worksheet. I want to be able
to show a separate register (I'm copying the layout of the main register)
that has basically pulled out the transactions of the specified type and have
a balance for that type (which will be different from the main register).

Ex:
Number Date Description of Transaction Class Credit
Debit Balance

100 1/1/2000 John Q Public P
350 1000
101 1/2/2000 General Electric U
400 650
D 1/3/2000 Deposit P
1000 1650
102 1/4/2000 Mary Johnson P
575 1075

I'd like to be able to pull all the "P"s onto a separate worksheet and run a
separate balance so I know how much is in the "P" account.

 

[ShaneDevenshire]

Hi,

You really don't need to extract the data to a different sheet, you could use:

1. SUMIF
2. DSUM

Or you can extract all the data of a given type by:
1. Using Filter, Auto Filter and copying the results
2. Using Filter, Advanced Filter and extracting the data to a new location
in the same sheet, or a different sheet. The last of these is a little
tricky.

 

[mscureman]

Of course I want to use the most difficult option. My middle name should
have been "Murphy". Ok, so I use the Filter/Advanced filter option, but it
only wants to let me put the data in the active worksheet. Is there a trick
I don't know for Excel 2002 to get it to let me put it on its own worksheet?
Should I be using Access instead?

 

[T. Valko]

it only wants to let me put the data in the active worksheet.

Is there a trick

Yes, sort of. Start the filter process from the destination sheet.

For example, if your data is on sheet1 and you want to extract some of it to
sheet2, start the process from sheet2.

Are you usng a criteria range? If you want to filter on Class = P...

On sheet2 use cells A1:A2 as the criteria range.

A1: Class
A2: ="=P"

--
Biff
Microsoft Excel MVP

Of course I want to use the most difficult option. My middle name should
have been "Murphy". Ok, so I use the Filter/Advanced filter option, but
it
only wants to let me put the data in the active worksheet. Is there a
trick
I don't know for Excel 2002 to get it to let me put it on its own
worksheet?
Should I be using Access instead?

 

[ShaneDevenshire]

Hi,

The tricky part of this approach is that on the destination sheet you should
1. already have your field titles for the target range and 2. you should have
one cell below the titles containing something, anything, dummy data.

The criteria does not need to be on the destination sheet, that range can be
anywhere.

--
Cheers,
Shane Devenshire

it only wants to let me put the data in the active worksheet.
Is there a trick

Yes, sort of. Start the filter process from the destination sheet.

For example, if your data is on sheet1 and you want to extract some of it to
sheet2, start the process from sheet2.

Are you usng a criteria range? If you want to filter on Class = P...

On sheet2 use cells A1:A2 as the criteria range.

A1: Class
A2: ="=P"

 

[mscureman]

Hi Shane,

I was just reading the post above mine about auto-populating separate
sheets based on the data in one column of a "master" sheet. This is exactly
what I'd like to do - Have a master check register with a master balance,
then have all the transactions of type "A" be copied onto a separate
worksheet that lists ONLY those transactions, including all the data in
columns B through G for that row - as in:

If the data in column E = "P", copy the data in columns B through G into the
next available row on Sheet2 (named "Payroll")

Would you review that post and let me know if that's the most efficient way
to accomplish this?

Hi,

The tricky part of this approach is that on the destination sheet you should
1. already have your field titles for the target range and 2. you should have
one cell below the titles containing something, anything, dummy data.

The criteria does not need to be on the destination sheet, that range can be
anywhere.

 

[mscureman]

Biff,

Thanks for your response. I don't know if I'm using a criteria range or
not - how do I set that up, and do I need to? I went through a more detailed
description of what I'm trying to accomplish in a response to ShaneDevonshire
if you'd like to read that.

Mark