# Auto Refresh the Drop-Down List in Excel Worksheet

#### David77

##### Board Regular
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

Best regards,

David

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### mumps

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
This formula worked for me: =OFFSET(Products!\$E\$2,0,0,COUNTA(Products!\$E:\$E),1)

#### Fluff

##### MrExcel MVP, Moderator

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

#### David77

##### Board Regular
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

##### Well-known Member

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

#### David77

##### Board Regular
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)
=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)
=OFFSET(Products!\$E\$2,0,0,COUNTA(Products!\$E:\$E),1)

#### David77

##### Board Regular
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

##### Well-known Member
You are very welcome and thanks to Fluff for pinpointing the problem.

Replies
1
Views
132
Replies
0
Views
55
Replies
7
Views
95
Replies
0
Views
150
Replies
5
Views
289

1,127,584
Messages
5,625,643
Members
416,124
Latest member
DeMoNloK

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back