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

Hi, I'm Elise, an independent advisor and I'd be happy to help with your issue.

I think the issue might be the ‘Source Range’! section, is that the name of the sheet you are getting the data from?

You are correct however you need to have the equals sign in the source box as well.

Kind Regards,

Elise

Answer 2

Hello Elise,

Thank you so much for your quick answer!

You are correct, my sheet is actually named "Products" (where I am getting the data from).

I renamed the formula to: =OFFSET(‘Products’!$E$2,0,0,COUNTA(‘Products’!$E:$E)-1) now

However, I am still getting the same error.

Is there anything else I can do?

Best regards,

David

Answer 3

Almost there, just change it to:

=OFFSET(Products!E2,0,0,COUNTA(Products!E:E)-1)

Kind Regards,

Elise

Answer 4

Or actually better to be:

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

Kind Regards,

Elise

Answer 5

Hello Elise,

Thank you so much!

I tried both formulas, and it still gives me the error:

I have no idea why it won't let me type in the formula