Auto Refresh the Drop-Down List in Excel Worksheet

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
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
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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:

1593000580688.png


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
Joined
Apr 11, 2012
Messages
10,152
This formula worked for me: =OFFSET(Products!$E$2,0,0,COUNTA(Products!$E:$E),1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 11, 2012
Messages
10,152

ADVERTISEMENT

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

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 11, 2012
Messages
10,152
You are very welcome and thanks to Fluff for pinpointing the problem. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top