LinkBack URL
About LinkBacks
A have a drop down list, with all the items help on a separate tab.
Is there a way to add new items to the list by just typing them in and they will be added to the list or do i need to enter them in my original list first?
A have a drop down list, with all the items help on a separate tab.
Is there a way to add new items to the list by just typing them in and they will be added to the list or do i need to enter them in my original list first?
You can make that named range dynamic, so that it automatically adjusts to new data being added to the bottom. Use Name Manager on the Formulas tab to see what the Refers To range is, and copy it into a post here, so that we can see which sheet and which column your data is in (or attach a sample Excel workbook as described in the yellow banner at the top of the screen), and the we can suggest a suitable formula to use.
Hope this helps.
Pete
Named ranges or structured tables.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
You could convert your list to a table. A table extends its range automatically when data is added. In the Data Validation dialog you need to refer to your table using the INDIRECT worksheet function.
For example: your table is called "Table2" and has a header called "MyItems", then enter the following in the "Source" field on the Settings tab of the DV dialog:
Formula:
=INDIRECT("Table2[MyItems]")
Ok Thanks everyone for the advice.
I have uploaded an example.
It has the sites tab and entry as a name table "Sites" the dropdown list links to these cells on the sites tab within the box.
It works ok for selecting from the list, I am still struggling to get it to be able to add new entries that would then be added to the sites list for future use, preferably in alphabetic order.
Any help much appreciated.
A table is dynamic by design so don't use its address like $A$1:$A$20
See file attached.
EDIT: try entering a fifth city at the bottom of the table and watch what's happening with both table and dropdowns on the other worksheet.
Last edited by GWteB; 12-16-2021 at 03:16 PM.
Thank You, that looks like what i need. In the revised version I have uploaded.Originally Posted by GWteB
Can I ask a couple of questions on this.
Do I need to update the table with the sites, or is it possible to type in the box and have the site table updated, it is fine if i need to update the site table first, i am just interested to know.
Also I have added a site number, i get this by using xlookup, is that the best way?
Last edited by gjjh25; 12-16-2021 at 04:08 PM.
Glad to help.
I'm not sure what you mean by this. Just to be sure, the table named Table1 on the worksheet named Sites is the source of and contains the elements for the data validation drop downs that you use in column B of the worksheet named Order.
It looks like there's a relationship between the site name and the site number. In that case I would keep the XLOOKUP.
Thank for the help, much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks