A real challenge - saving input in cells to another location

[bergen]

I am after two things here:
1: An assessment of what I am trying to do would be feasible in any way using excel?
2: If it can be done, then a solution to the issue or some hints on where to start.

What I want to achieve:

I want users to give input in five cells on an excel sheet like this:

Product	High	Low	Reason	Your name
883	x		random	Peter

<tbody>
</tbody>

Then click a button or something and have this data transferred to a table on another tab that collects inputs as they come in. Looking like this:

Type	Product	High	Low	Reason	Employee
800 series	882	x		XYZ	Peter
	882		x	ZYZ	John
	883	x		XXY	Peter
900 series	982	x		YYZ	Peter

<tbody>
</tbody>

Every input should then create a new line under the appropriate product ID number

The reason for doing this rather than just having people adding the input manually is threefold:
1: People should not be allowed to see other peoples input (locked sheet)
2: Finding the right place to enter input is a challenge since there are MANY ID's
3: For ease of use

Any suggestions?
/Markus

 

[Col Delane]

Hi Markus
This is not such a challenge if you have the right tools (experience/knowledge) at your disposal!

Proposed solution

1. Create two identical sheets, each with the five heading rows, say in B1:F1, for which input is required.
2. Name one sheet "Input" and the other "Data"
3. On Data sheet, add a column heading to A1 for Type. I don't know how you intend to populate this field but suggest that you could calculate it mathematically by using a formula like this in A2:A? =ROUNDDOWN(B2,-2).
4. Hide Data sheet
5. On Input, leave one blank row under the headings and in range B3:F3 enter "\-" (minus quotes) to create a "dividing fence/boundary". B1:F2 is now your data entry table.
6. In B4:B? enter the acceptable Product ID No.s.
7. Apply Data Validation ('DV') to B2 with "Allow" = "List" and "Source" = the range of Product IDs enter B4:B?. Populate the 'Input Message' and 'Error Alert' tabs on the DV window as you see fit.
8. Do the same for Reason and Employee fields if there is a list of acceptable values (i.e. you have a fixed choice of reasons, and employee names.)
9. In C4 enter "X"
10. In D4 enter "X"
11. Apply Data Validation to C2 with "Allow" = "Custom" and "Formula" =AND(Upper(C2)=C4,D2=""). This will allow the user to enter "X" or "x" only, but only if D2 is blank.
12. Apply Data Validation to D2 with "Allow" = "Custom" and "Formula" =AND(Upper(D2)=D4,C2=""). This will allow the user to enter "X" or "x" only, but only if C2 is blank.
13. Select G1:XFD1 (i.e. all columns to right of the input table), and press Ctrl+0 (zero) to hide these columns.
14. Select all rows below you DV option lists and hide these rows.
15. Other steps could be taken to unlock the input cells, protect the worksheet etc. as required to limit user access.

Input sheet now has a defined area in which users can input the required data.

All you need then is:

1. a macro button on Input sheet for users to click to "Add input to database" or similar
2. some VBA code (to which the button is linked) that will:
   1. unprotect the Workbook - to allow VBA to access to the Data sheet but not display it.
   2. copy the input data to the next blank row of the table on Data
   3. re-protect the Workbook
   4. clear the contents from the Input table
3. protect the Workbook (Structure and Windows) with a password only you know
4. protect the VBA code with only a password you know so users cannot ascertain the password used to protect the Workbook.

BTW, what do you mean by

2: Finding the right place to enter input is a challenge since there are MANY ID's

I hope that gives you some direction.