Hi

My advice is: redesign this! Otherwise you'll have problems whenever you
want to create some generalized report.

Unless you have much more than 100 entries in every month, Excel will do.
When you have more entries, split the table to yearly sheets, or switch p.e.
to Access. When you'll use excel, use worksheet design like:

Month, Manager, Project, Category, Identifier, Cost

Month: Or date in form yyyy.mm.01, formatted as Custom "yyyy.mm", or a
number of month in format yyyy.mm. The best will be, when you have some
(hidden) sheet with valid month values, defined as named range (in more
advanced design a dynamic named range, where available months depends on
current date), and in your table you can enter only months available in this
named range (or select them from drop-down). For this you can use data
validation list.

Manager: Manager name. My advice is to use data validation here too - enter
the list of managers into table on separate sheet, and define it as named
range. As probably during 12 years managers list does change, this has to be
a dynamic named range - so you can add new managers whe needed.

Project: Project code. Here too a data validation list will be the best
solution.

I'm not sure about Category and Identifier. When those are bound p.e. to
project, then there is no need for user to enter them. You enter them p.e.
into Project lookup table as additional columns, and in your table you use
VLOOKUP to get them when Project is selected.

In your table you always must have some amount of empty rows (at least for
month) with validation lists, formulas and cell formats ready (you must
include some checking for empty rows into formulas - p.e. the formula result
is displayed only, when month and project are entered, otherwise an emty
string is returned. You can prepare all rows for 12 years at start, or you
copy the last empty row down every month, or you create an workbook's Open
event, which checks the number of preprepared rows every time the workbook
is opened, and adds new ones when there isn't enough of them.

Probably you have a single row for a project in month (or for some other
combination of columns). A good idea will be to implement the conditional
formatting for datarow, so that cells in datarow are colored differently
whenever such combiation returns a non-unique result (you can't use data
validation to restrict such entries, as you have to use data validation
lists in those columns). Whenever the datarow is colored, the user does
know, that the entry exists, and he/she has to clear the last entry, find
the existing one, and edit it (or leave it as it is).

To find any entry easily, use autofilter feature. To use autofilter without
problems, you must have a single header row directly before first datarow.
When you need moe rows in header, there must be an empty row between last
header row, and first ones (you can hide this empty row, but it must be).

Into header you can include SUBTOTAL formulas, depending on used parameters
they return various aggregate values based on filtered data. P.e. using
SUBTOTAL with parameter 9 on Cost column, you get summary cost of filtered
data - set the autoflter to project, and you get money spent on this project
so long.

And of course, use Freeze Panes feature to ensure, that table header is
always visible.

To get more sophisticated reports, design special report sheets, which will
display p.e. summary data, or data accordingly some parameters. And of
course with such design you can easily create various pivot charts or pivot
tables based on your data.

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"philc" wrote in message
oups.com...
i have a table, 5 fields (columns) for manager name, project name, etc,
and another 144 columns giving money spent for each month
so column headers would be:-
manager name, project name, project code, category, identifier
then months going from jan-05 upwards for 12 years, so table has 149
columns and as many rows as records.

what i need is a method where a user can easily update this table
without having to find the correct row / column themselves.

how can i get a value from a user entry cell into the correct cell in
the table.