Glad to see you eventually got Excel jeanie working.
You will need vba to accomplish your goal. You haven't said just would trigger the transfer of data from the form to the Central Sheet, but here is one way.
1. On the 'Blank Form' sheet, create a 'Submit' cell. I have used A8. Double-clicking this cell will trigger the transfer once you have entered all the data.
2. Right click the 'Blank Form' sheet name tab and choose 'View Code'.
3. Copy the code below and Paste into the main right hand pane that opens at step 2.
4. Close the VB window, enter data on the form and double click A8. The data should then be on the Central sheet.
<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br> <SPAN style="color:#00007F">Dim</SPAN> AgentCell <SPAN style="color:#00007F">As</SPAN> Range, DateCell <SPAN style="color:#00007F">As</SPAN> Range, ScoreCell <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Dim</SPAN> AgentHeader <SPAN style="color:#00007F">As</SPAN> Range, DateHeader <SPAN style="color:#00007F">As</SPAN> Range<br> <br> <SPAN style="color:#00007F">Set</SPAN> AgentCell = Range("B6"): <SPAN style="color:#00007F">Set</SPAN> DateCell = Range("H6"): <SPAN style="color:#00007F">Set</SPAN> ScoreCell = Range("H8")<br> <br> <SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$8" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'<-- Change this to match your 'Submit' cell</SPAN><br> Cancel = <SPAN style="color:#00007F">True</SPAN><br> <SPAN style="color:#00007F">If</SPAN> AgentCell.Value = "" <SPAN style="color:#00007F">Or</SPAN> DateCell.Value = "" <SPAN style="color:#00007F">Then</SPAN><br> MsgBox "Cannot transfer data while Agent or Date is incomplete"<br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">With</SPAN> Sheets("Central Sheet")<br> <SPAN style="color:#00007F">Set</SPAN> AgentHeader = .Rows(1).Find(What:=AgentCell.Value, LookAt:=xlWhole, _<br> MatchCase:=False, SearchFormat:=False)<br> <SPAN style="color:#00007F">Set</SPAN> DateHeader = .Columns(1).Find(What:=DateCell.Value, LookAt:=xlWhole, _<br> MatchCase:=False, SearchFormat:=False)<br> <SPAN style="color:#00007F">If</SPAN> AgentHeader <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Or</SPAN> DateHeader <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> MsgBox "Unable to transfer data"<br> <SPAN style="color:#00007F">Else</SPAN><br> .Cells(DateHeader.Row, AgentHeader.Column).Value = ScoreCell.Value<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
Excel Workbook |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
4 | Evaluator: | | | | | Account No/ MSISDN | | |
---|
5 | | | | | | | | |
---|
6 | Agent: | Claire | | | | Interaction Date: | | May-10 |
---|
7 | | | | | | | | |
---|
8 | Submit | | | | | Score: | | 13.60% |
---|
9 | | | | | | | | |
---|
10 | | | | | | | | |
---|
11 | | | | | | | | |
---|
12 | | | | | | | | |
---|
13 | | | | | | | | |
---|
14 | | | | | | | | 1.00% |
---|
15 | | | | | | | | 2.60% |
---|
16 | | | | | | | | 10.00% |
---|
17 | | | | | | | | |
---|
|
---|
Excel Workbook |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | | Mike | Catherine | Karen D | Ashleigh | Claire | Cathy | Bridget |
---|
2 | Jan-10 | | | | | | | |
---|
3 | Feb-10 | | | | | | | |
---|
4 | Mar-10 | | | | | | | |
---|
5 | Apr-10 | | | | | | | |
---|
6 | May-10 | | | | | 13.60% | | |
---|
7 | Jun-10 | | | | | | | |
---|
8 | Jul-10 | | | | | | | |
---|
9 | Aug-10 | | | | | | | |
---|
|
---|