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?
 

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
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
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?
 

Forum statistics

Threads
1,085,033
Messages
5,381,309
Members
401,733
Latest member
Kabasa007

Some videos you may like

This Week's Hot Topics

Top