I do remember seeing one nice way of populating a table with the use of vba such as :
Sub EnterName() Dim col As Single, Lrow As Single Dim tmp As String col = Application.WorksheetFunction.Match(Range("C2"), Range("B4:H4"), 0) - 1 tmp = Range("B" & Rows.Count).Offset(0, col).Address Lrow = Range(tmp).End(xlUp).Row Range("B1").Offset(Lrow, col).Value = Range("E2").Value Range("B1").Offset(Lrow, col + 1).Value = Range("F2").Value Range("E2").Value = "" Range("F2").Value = "" End Sub
How would it be possible to modify the code to populate a table such as: the first column header could be chosen from the drop-down list as well as the first row header. In other word the location of the data to be entered could be determined by the row AND the column.
C2 should be a data validation (list).
B4:H4 (here only 7 columns) would be the headers to match the value in C2.
A second data validation should make reference to Column A.
1st data validation correlated to Column's Headers (B to ect)
2nd data validation correlated to values in Column A ("Row Header")
As for the kind of values, being headers they would most likely be (but not limited to) text strings.
Create drop down lists
- Select cell C15
- Go to tab "Data"
- Click "Data validation" button
- Select "List" in Allow: field
- Select cell range B1:H1 in Source: field
Repeat above steps with cell C16 and cell range A2:A13
- Press Alt+ F11
- Right click on your workbook in project explorer
- Click Insert
- Click Module
- Paste vba code to module window
Sub AddValue() Dim column, row As String Dim c, r As Single With Worksheets("Sheet1") If .Range("C15").Value = "" Or .Range("C16").Value = "" Then Exit Sub column = .Range("C15").Value row = .Range("C16").Value c = Application.Match(column, .Range("B1:H1"), 0) r = Application.Match(row, .Range("A2:A13"), 0) .Range("A1").Offset(r, c).Value = .Range("C17").Value End With End Sub
- Go to "Developer" tab
- Click "Insert" button
- Click "Button"
- Create a button
- Assign macro "AddValues"
- Click OK
- Change button text
Download excel *.xlsm file
Add values to a table.xlsm