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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0
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,"?*"))
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top