Auto Refresh the Drop-Down List in Excel Worksheet

Question

Hello Everybody,

First of all, thank you so much for using your time in assisting me with this matter and reading this inquiry. It is greatly appreciated!

I am currently making an order form in Excel, where I am pulling my product data (material description) from a sheet I have named "Products":

After this, I made a new sheet with an Order form, which looks like this:

Now, I want to make a drop-down list of all the materials in "Product". However, I would also like for this list to automatically update itself whenever I add a new entry to the "Products" list of materials (current range is E2:E97, but this can easily go above E100 once I started editing). In order to create this automatic update feature I saw this thread online: https://www.datanumen.com/blogs/2-methods-auto-refresh-drop-list-excel-worksheet/

Now, please do note that I am not an extremely experienced Excel user and thus I am not 100% sure if I did the formula from the link correctly, but I edited the formula that was provided in method 1 to apply to my own sheet, where I named it:

=OFFSET(‘Source Range’!$E$2,0,0,COUNTA(‘Source Range’!$E:$E)-1)

However, when I try to enter this formula I get the following message:

I tried putting in '= instead in my formula, so it goes like this: '=OFFSET(‘Source Range’!$E$2,0,0,COUNTA(‘Source Range’!$E:$E)-1)

This removes the error, but makes the formula invalid, so the "Product" drop-down list just looks like this: 

I am very confused as to how I can solve this problem. If anybody can give me nice inputs/solutions, I would greatly appreciate it!

If you need further information, screenshots, or anything else, please do not hesitate to let me know either :)

Thank you for your time.

Best regards,

David

Answer 1

UPDATE:

I used the following formula:

=OFFSET(Products!$E$2;0;0;COUNTA(Products!$E:$E);1)

With ; instead of ,

This fixed it and now it works.