Auto Refresh the Drop-Down List in Excel Worksheet

[David77]

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: 2 Methods to Auto Refresh the Drop-Down List in Your Excel Worksheet - Data Recovery Blog



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

 

[mumps]

Try this formula and replace "Sheet1" with the name of the sheet containing the product list. If the sheet name has any spaces in it, enclose the name with apostrophe's: 'Sheet Name'
=OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$E:$E),1)

 

[David77]

Try this formula and replace "Sheet1" with the name of the sheet containing the product list. If the sheet name has any spaces in it, enclose the name with apostrophe's: 'Sheet Name'
=OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$E:$E),1)

Hello Mumps,

Thank you so much for your time and assistance!

Unfortunately, I tried following your formula and it did not work. I am still getting the same error:

The sheet in which I am pulling my data from is named "Products" as showcased in the photo.

Any ideas?

Best regards,
David

 

[mumps]

This formula worked for me: =OFFSET(Products!$E$2,0,0,COUNTA(Products!$E:$E),1)

 

[Fluff]

What do you normally use as separators in your formulae? Is it commas or semi-colons?

 

[David77]

This formula worked for me: =OFFSET(Products!$E$2,0,0,COUNTA(Products!$E:$E),1)

I dont know why, but it is not working for me.

Any ideas as to what the error might be?

 

[mumps]

Have a look at Fluff's response in Post #5.

 

[David77]

What do you normally use as separators in your formulae? Is it commas or semi-colons?

Hello Fluff,

I typically use semi-colons = ;

However, I tried doing it with semicolons too earlier and it still comes with the same error if I change the formula to:

=OFFSET(‘Products’!$E$2;0;0;COUNTA(‘Products’!$E:$E)-1)
Instead of:
=OFFSET(‘Products’!$E$2,0,0,COUNTA(‘Products’!$E:$E)-1)

Or with Mumps formula:
=OFFSET(Products!$E$2;0;0;COUNTA(Products!$E:$E);1)
Instead of:
=OFFSET(Products!$E$2,0,0,COUNTA(Products!$E:$E),1)

 

[David77]

UPDATE!

I tried doing it with Mumps formula again and for some reason it worked now:

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

Thank you so much guys! I truly appreciate it, you have saved me a heap of trouble

Please have a very nice and enjoyable day, you definitely deserve it!

 

[mumps]

You are very welcome and thanks to Fluff for pinpointing the problem.