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,
What field(s) uniquely identify a row so we know which one to update?
What fields do you want to update - is it (only) money spent? And can new
projects and associated details be added?

If some(/all) of the (first 5) fields have a limited number of values, then
use of comboboxes to select your "search" fields is one approach.

"philcud" wrote:

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.

the first five fields uniquely identify a row, so this plus the month
and year will be the unique id.
i am familiar with the drop down boxes, but how to know which cell to
enter the user entered value?

Hi again,
I think the only option is to add an extra column at the
beginning (i.e. will be column A) which contains a "key" composed of the 5
identifier fields concatenated together. However, for this (or any other
matching to work), you need to ensure the search field contents match the
data in your file; extra blanks/mistyping will cause mismatches.

You can then do a MATCH to find the row. Having found the row, you can
calculate the column based on a date.

Sample code is shown below. It assumes row 1 is a header row and the
"SearchKey" is composed of the 5 identifier fields.

Function GetRow(ByVal SearchKey As String)
Dim res As Variant
' "Primary_Key" is named range based on Column A
res = Application.Match(SearchKey, Range("Primary_Key"), 0)
If IsError(res) Then
MsgBox SearchKey & " not found"
GetRow = 0
Else
MsgBox SearchKey & " Found in row " & res + 1 ' Allow for header
GetRow = res + 1
End If
End Function


' My test ......

Sub test()

n = 3 ' Test for key in row 3 of test data
' Create a search key (data in columns B to F)
' Replace by Comboboxes ??
SearchKey = ""
For i = 2 To 6
SearchKey = SearchKey & Cells(n, i)
Next i
' Find the row for this key
Keyrow = GetRow(SearchKey)
' InYear/inMonth are year/month where data is to be placed
inYear = 2006
inMonth = 7
' Amount is data to be entered
Amount = 1234.56
' Calculate Column number assuming start of Jan-05 (in Column G)

KeyCol = (inYear - 2005) * 12 + inMonth + 6

Cells(Keyrow, KeyCol).Select ' Select not really rquired
ActiveCell = Amount ' or Cell(KeyRow,KeyCol)=Amount

End Sub

HTH

"philcud" wrote:

the first five fields uniquely identify a row, so this plus the month
and year will be the unique id.
i am familiar with the drop down boxes, but how to know which cell to
enter the user entered value?