Referencing a list throughout multiple sheets that automatically updates

HalKinsey

New Member
Joined
Jun 11, 2015
Messages
6
I am working on a project as a contractor, and I am having a somewhat interesting problem. The company sells chemical products and I was instructed to have one page where the products are listed and can be updated throughout the document. I was also instructed to use type selective drop-down boxes (I used combo boxes) for each day, for an entire monthly report. My current issue is having to manually update 300 combo boxes of the products list because I don't know of any way to have them update automatically when the 'Products List' page has been updated. Any ideas?
 

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).

aware073

Board Regular
Joined
Jun 5, 2015
Messages
54
Can you post an example sheet? You should be able to just put a formula on the other pages that will update each page as you fill the first one in.
 

HalKinsey

New Member
Joined
Jun 11, 2015
Messages
6
Are you asking me to post part of the project? I will do whatever I can to figure this out. Youtube is blocked at work so I currently have no other way to solve this problem.
 

HalKinsey

New Member
Joined
Jun 11, 2015
Messages
6

ADVERTISEMENT

From my understanding of dynamic named ranges, that will only alter the number of names that appear, correct? I already built in spaces for 20 new products at the bottom of my 'Product List' page.
 

Stridhan

Well-known Member
Joined
Mar 5, 2014
Messages
568
dynamic named range will expand if your list expands.
If youre using a named range as input in your combobox it will expand with the productlist, i think :)
 

HalKinsey

New Member
Joined
Jun 11, 2015
Messages
6

ADVERTISEMENT

My biggest question, since I am working with combo boxes, they all have a ListFillRange referencing a name defined by a location on a worksheet. If I start adding products that way, the name definition will no longer be valid, will it?
 

HalKinsey

New Member
Joined
Jun 11, 2015
Messages
6
This is so difficult for me to explain... I have 10 drop down boxes per sheet, and one sheet per day that lists all of our products, 10 different times. I found a formula online that will make suggestions for the most likely product based on the letters typed. I will post the code that I used...

=--ISNUMBER(IFERROR(SEARCH('Monday Report 1'!$A$5,DropMon1!A2,1),"")) which then returned a value which the next column used...
=IF(B2=1,COUNTIF($B$2:B2,1),"") this gives suggestions to the next column with this code
=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($C$2:C2),$C$2:$C$100,0)),"") The final line of code, which returns a name, is how I defined the value of all of my names.
=$D$2:INDEX($D$2:$D$100,COUNTIF($D$2:$D$100,"?*"))
 

Stridhan

Well-known Member
Joined
Mar 5, 2014
Messages
568
sorry I dont get it.
Didnt you want to add all products in a changing productlist to all comboboxes?
Today you use a named range?
You want that named range to be dynamic?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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