Hi,

I have a spreadsheet Expense Journal designed for entering expenses in two
currencies, one for Dollar and one for a local currency. It has two sections
of rows, the top is for Dollar Expenses and uses no exchange figures
because the spreadsheet converts everything to Dollars. This Dollar section
has five columns:

Date Project # Account # Description Dollar
Amount

In the blank spreadsheet that they start anew each quarter, there are only
two rows setup for Dollar expenses because most of their expenses will be in
Local currency.

Below the Dollar section are 15 rows allocated for Local Currency expenses.
Here there are six columns:

Date Project # Account # Description Amount Dollar Amount

In this case, the Dollar Amount is calculated from the Amount column from
an exchange rate entered elsewhere.

Both the Dollar section and the Local Currency section can be expanded by
use of a macro. At the end of a quarter there might be 10 Dollar entries and
200 Local Currency entries.

The Project # must be a two or three digit number (there may be hundreds to
choose from, but typically only 2-5 will be used in any single Expense
Journal), the Account # is a drop down box of 20 accounts by name, e.g.
Equipment
Medical
Misc. Expenses
Office Supplies
etc.

All the above goes from Cols B to I.

Because there are headers between the Dollar and Local Currency rows, in
order for the array below to work, I have it set up so the info from the
Project #, Account #, and Dollar Amount rows is copied to Col.Z:AB (I'll
call this the Remote Data) so that the arrays for these three names ranges
(Project, Account, Amount) can be contiguous.
==============
Then in Cols. L to Q, I have a summary matrix with all 20 Accounts listed in
20 Rows in Col L. To the right of this is 5 columns (M:Q) where I want to
summarize the up to 5 separate Project #s with the expenses for each
Project # in each Account category. This matrix is 21 down and 6 wide. (21=
20 account rows plus one header row for up to five Project #'s to be
listed). This matrix receives info only from the Remote Data.

With this matrix I want to summarize all the expenses from all Project# &
Account# combinations. But I want the headers of these 5 columns to populate
automatically with Project #s. But I have no idea which Project #s the user
will end up using (could be 3 out of 400) as they enter their expenses.
Also, I don't know if their first expense will be a Dollar expense or a
Local currency expense. So in the Remote Data, the first entry might be on
the first line (which would be from the Dollar section), or from the third
line (which would be the Local currency section).

So if their first five expenses are entered as follows:
Project Account $Amount
222 Equipment 120
321 Misc 25
42 Medical 75
321 Misc. 50
42 Equipment 80

Then the matrix would total as follows:
222 321 42
Equipment 120 80
Misc 75
Medical 75
=============================
My question is, how can I get those five header columns of the matrix to
populate automatically. Previously someone offered this formula to which I
made a few changes. M4 is the first of the 5 headers.
=IF(ISERROR(INDEX(IF(LEN(Project)<2,"",Project),MA TCH(0,COUNTIF($M$4:M$4,Project&""),0))),"",INDEX(I F(LEN(Project)<2,"",Project),MATCH(0,COUNTIF($M$4: M$4,Project&""),0)))
Where I have LEN<2 they had ISBLANK, and where I have Project, previously
there was a range. But I made a few changes to my setup and the formula no
longer works properly - kindof works but leave gaps in the five headers.
First and third header might have a project #, while the second is empty.
I'd like them to populate left to right. I know this is made difficult by
not knowing if the first expense will be entered in the dollar section or
local currency section.

I have =IF(LEN(Z19)2,Z19,IF(LEN(Z21)2,Z21,"")) in the first header (M4)
and it seems to work, but that's the easy one.

Since the above formula is way over my head, I'd sure appreciate anyone that
can tweak it to make it work in the other four headers.

Thanks for taking the time to look at this. It's the last thing I need to
finalize before I can begin sending out this Expense Journal for folks to
use overseas and it would feel so good to bring closure to this.

Thanks very much,

Harold