Data Validation and VLOOKUP function

Hello. Happy to be here on the forum!

I have recently switched from libreoffice to microsoft office 2016 because there are many more features to 2016 that I need. I have run into a problem I can not solve myself (which is odd)

In libreoffice it would work no problem but in office it does not.

Here's what I am doing:

I have 2 sheets in a workbook. sheet 1 is a list of data. Sheet 2 is a interactive form type of page that pulls data from sheet 1 and I print.

On sheet 2 I use data validation in cells to provide a drop down list of the specific cells on sheet 1.

On sheet 2, next to the data validation field, I then use a VLOOKUP to retrieve the data in the fields from sheet 1 based on what is selected in the data validation field.

So in my VLOOKUP formula my first argument is the data validation field.

Here's the problem:

In libreoffice the VLOOKUP formula would complete the formula with the cell that data validation was referencing from sheet 1 giving me all the information in the cells next to whichever I selected from sheet 1.

So whenever I changed the selection in data validation my VLOOKUPS would change with the data validation to whichever cell data validation was referencing from sheet 1.

In MSOffice when I use the data validation cell in the first argument for my VLOOKUP MS Office references the actual cell on sheet 2 and doesnt recognize that it is a data validation field. That makes my VLOOKUP formulas reference sheet 2 and not sheet 1.

My question:

How do I make the VLOOKUP formula access the data through the data validation selection and not the cell the data validation is placed in.

Much appreciated.
Andy

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.