1

I'm working on a form with many rows that user need to fill. Often they drag down (drag-and-drop) cell values to popluate rows that have the same values over several rows. Auto-increment (see below), causes multiple false values in my Excel form.

enter image description here

How can I permanently disable auto-increment to obtain the following:

enter image description here

I know the easiest way would to hold down Ctrl while dragging down, but users filling the from typically do not know this. Same applies to other solutions to be applied by the users themself.

I may also disable drag-and-drop completely in the Excel settings under advanced, but this would limit the functionality of the form.

enter image description here

Any ideas? Ready to use VBA solutions are welcome.

Note: I do not search a solution avoiding the problem for me, I need a solution avoiding the problem for users filling the form!

Improve this question
0

2 Answers 2

Reset to default
1

Instead of filling in one cell then dragging the cell handle, select all the cells where you want a value, then type and press Ctrl+Enter to fill that value in all of them (or a formula, or whatever).

If you already have a value, select all the cells where you want it (continuous or otherwise), then press F2 to go into edit mode, then Ctrl+Enter.

Improve this answer
4
  • A simple thing I do is add values to the first few row 1. Hallo 2. Hallo then drag things down. The generally deals with that.
    – BradR
    Commented Jul 12, 2021 at 12:15
  • A said, it's not me filling the form and users are not always good in following instructions. So I would love to turn off auto-increment to stop users messing up the submitted data.
    – MsGISRocker
    Commented Jul 12, 2021 at 12:46
  • As a general rule, trying to fix a people problem using technological means is not a good way to fix things. To put it another way, if you try to make something idiot-proof, they'll just go and find a bigger idiot. If the real problem is lack of user knowledge or training, give them some training (or just better notes). You might be surprised how many people genuinely want to find newer and better ways to work, saving them time, effort and frustration.
    – AdamV
    Commented Jul 12, 2021 at 14:47
  • @Adam: I'm not so sure. That's all what excel is about. Using tech to fix problems.
    – MsGISRocker
    Commented Jul 14, 2021 at 16:55
0

You can enter the first value in another Worksheet and Hide it, then use the formula to return the value, for example:

enter image description here

Improve this answer
2
  • As mentioned, users are to fill a form and often they use dag -down to fill several rows with the same values. I were to know the values they enter up-front, I could use your approach, but I don't know it.
    – MsGISRocker
    Commented Jul 14, 2021 at 16:54
  • Do you have more samples about your problem?
    – Lee
    Commented Jul 15, 2021 at 2:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .