Can I get multiple instances of a value (and data) to another location?

[Gordon Abbot]

I recently was appointed treasurer of a church.

I would like to make my life a little easier and be able to gather the
employee check number, date paid, name and amount from the journal
entries to a separate sheet used for income taxes. Journal entries are
all my monthly checks to many different people and companies. We pay
weekly so I am looking at 4-5 entries of the same name in a month.

For example, the Pastor. I want to get every weekly salary payment from
the journal and have each entry in a table in another workbook. So the
other work book would have all four or five payments to the one person
together with date, check number and amount.

The non-elegant way is to sort, copy and paste it in the new location or
do it manually. I would like excel to do it automatically, preferable
using functions, not macros or vbasic.

The check number, date, etc are in columns.

Any help appreciated.

GA

 

[Harlan Grove]

I recently was appointed treasurer of a church.

I would like to make my life a little easier and be able to gather the
employee check number, date paid, name and amount from the journal entries
to a separate sheet used for income taxes. Journal entries are all my
monthly checks to many different people and companies. We pay weekly so I
am looking at 4-5 entries of the same name in a month.

Are these journal entries entered into Excel? It'd appear so. [Tangential
comment: you'd be better off using Quicken or something similar for this.]

For example, the Pastor. I want to get every weekly salary payment from the
journal and have each entry in a table in another workbook. So the other
work book would have all four or five payments to the one person together
with date, check number and amount.

Another work_book_ or work_sheet_ ? You mentioned 'sheet' above but
'workbook' here.

The non-elegant way is to sort, copy and paste it in the new location or do
it manually. I would like excel to do it automatically, preferable using
functions, not macros or vbasic.

The check number, date, etc are in columns.

The generic way to do this is to name the source data range in the receiving
worksheet. That is, go to any cell in the worksheet into which you want to
pull this data, run the menu command Insert > Name > Define. Enter a name
like JournalData, move to the Refers to field and use normal cursor key,
[Ctrl]+[PgUp]/[PgDn] and [Ctrl]+[F6] keys to locate and select the entire
source data range (possibly more than just the cells already containing
entries), and click OK. Whether the journal data is in a separate worksheet
or workbook, this way of defining the name will ensure that the receiving
worksheet can locate it.

I'll assume check number is in the 1st column, date in the 2nd, payee name
in the 3rd and amount in the 4th, they'd be pulled into the receiving
worksheet in columns A through D starting in row 5, and the payee name
you're seeking is entered in a cell named Payee. Pull the first matching
journal entry using formulas like

A5:
=INDEX(JournalData,MATCH(Payee,INDEX(JournalData,0,3),0),1)

B5:
=VLOOKUP(A5,JournalData,2)

C5:
=Payee

D5:
=VLOOKUP(A5,JournalData,4)

Pulling the second and subsequent entries are a little trickier. The formula
for the next check number in A6 would need to be an array formula. The
formulas in the other columns aren't much diffent than their counterparts in
row 5.

A6 [array formula]:
=IF(SUMPRODUCT(--(INDEX(JournalData,0,3)=Payee))>ROWS(A$5:A5),
INDEX(JournalData,MATCH(1,(INDEX(JournalData,0,1)>A5)
*(INDEX(JournalData,0,3)=Payee),0),1),"")

B6:
=IF(A6="","",VLOOKUP(A6,JournalData,2))

C6:
=IF(A6="","",Payee)

D6:
=IF(A6="","",VLOOKUP(A6,JournalData,4))

Then select A66 and fill down as needed.

This assumes you don't do foolish things like list the payee differently for
different checks to the same person, e.g.,

Dr Joe Blogs
Joseph Blogs
Joseph K. Blogs
Reverend Joe Blogs, M.Div.

If you do have differing entries like this, you should embrace the Job-like
trials & tribulations God has sent you to test your faith (meaning: don't
expect miracles from Excel).

 

[Anne Troy]

Try this, Gordon:
http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com