Autopopulate using a dropdown menu

For Part 2 of your question, the data validation list must be from sequential cells. You could, for example, in J1:J3 put = A2, =A24, =A50 and then use J1:J3 for your data validation.

For part 1, take a look at what I did in the attachment. Choose from the dropdown in A1 and the cells from the appropriate voucher (on sheet2) automatically fill. I'll be back tomorrow if you have questions (if that's what you are looking for).

Okay, in A1, I have the dropdown list of vouchers using data validation. Let me know if you don't know how to do that (or any of the following steps).

On sheet 2 are all of your vouchers. First name them. Select the range and in the name box (where you normally see the cell address, i.e. A1), type in voucher1, enter.
Names should be the same as the list in your dropdown box minus spaces. Names can't have spaces.

Going back to Sheet1, select an area equal to the range size of your largest voucher (ideally, make all your vouchers the same size). Type in
=IF(A1<>"",INDIRECT(SUBSTITUTE(A1," ","")),"") and instead of ENTER, use CNTRL SHFT ENTER. This enters your formula in all the selected range as an array. If you click on it, you'll see they have brackets around them. This formula tells Excel to take the value in A1 and remove the spaces. Then, the INDIRECT tells Excel, not to treat this as text but as a reference, the reference being your named range. Because it's entered as an array, it puts the each relative cell on sheet2 into the appropriate cells on sheet1.

If your vouchers have blank cells in them, the formula will return a 0. To avoid this, instead of leaving those cells blank on sheet2, enter =""

Any questions?

Formulas cannot carry over formats. If they all have the same formatting, then you should format the cells on Sheet1 to duplicate the formatting on sheet2. If it's more complicated than that, you may need a VBA solution and should post in the Excel Programming folder. Hope that helps.