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.

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.

many thanks for that reply, i have racked my brains to work out how to
do this without redesigning the current table to a normalised database
style table that you suggest, but i think that it is worth doing.
a question - how do i get data from the format i have now, (a table of
363 projects (rows), by 123 columns (months) into a normalised format?

Hi

Let you have the current table on sheet OldData.

Create an empty sheet, and an empty table there with structure p.e. (I
assume project code is unique for project, with project name, category and
identifier same for same project code):
Month, Manager, Project, Cost

Copy all values from columns A:C in OldData to columns B:D of new table;
Enter the first month number into first cell in Column A of new table, and
copy it down for whole table;
Copy manager and project information from first 363 rows to next 363 rows,
enter next month number into sell in column A, and copy this month number to
all 363 rows;
Repeat this until all rows for months with costs available are copied.

Copy data from all 363 rows in column F (I assume this is the column for
1st month costs) of OldData to first 363 rows in column E of new table;
Copy data from all 363 rows in column G of OldData to next 363 rows in
column E of new table;
etc until all costs are copied.

Surely you have aq lot of rows now with no value in Cost column. Set
autofilter on, set autofilte to Cost is empty, and delete all such rows (you
have to do this row-wise), or set the autofilter to Cost is not empty, copy
all filtered data to new sheet (you can do it with a single go), and delete
ptreviouis new table.

From OldData, create 2 pivot tables. In first one, count all managers - you
get a manager list. Copy it to separate sheet.
In second one, count all projects. Copy the projects list to separate sheet,
and use VLOOKUP to retrieve project names, categories and identifiers from
OldData into Projects table. replace all values with formulas.

In your new data table, use VLOOKUP to get project names, categories and
identifiers into according columns. (maybe you start with defining named
ranges here?)

Your data are converted - you can delete OldData, or move it to separate
workbook for case soething goes wrong. Further you can define named ranges,
implement data validation, etc.


Arvi Laanemets


"philc" wrote in message
oups.com...
many thanks for that reply, i have racked my brains to work out how to
do this without redesigning the current table to a normalised database
style table that you suggest, but i think that it is worth doing.
a question - how do i get data from the format i have now, (a table of
363 projects (rows), by 123 columns (months) into a normalised format?