Forum Discussion
Why do dropdown lists appear differently on different computers and can it be changed?
If data validation list is based on the structured table located in the same sheet of the workbook, it expanded automatically with expanding of the table.
If table is on another sheet of same workbook, that doesn't work. As workaround for the drop-down list you may use formula like
=OFFSET(Sheet2!$A$2,0,0, COUNTA(Sheet2!$A$2:$A$500) )instead of the direct reference
=Sheet2!$A$2:$A$500
Hi! Thank you. 🙂 She said she was updated, I'll ask her to check again. 🙂
Thank you also for your suggested solution. 🙂
I tried it and realized that something I didn't mention in my original post might make a difference:
Both the worksheet with the tables and the worksheet that references the tables are protected. Not with a password but the preference is for the end user to not have to unprotect the worksheets to add additional rows and update the source code for the data validation.
I did what you said and resized the table (it's now 7 rows plus header).
I then protected the worksheet with the table and the worksheet that references the table.
I then tried to add a row to the table on the first worksheet and it didn't work. I tabbed through the row and it tabbed back to the beginning and down to the next row but the row didn't adopt the table design, nor were the other cells in the row formatted as they should have been (percentages, dollars, etc.).
When I went back to the second worksheet (also protected) where the table from the first worksheet is referenced, the test name that I added wasn't available in the dropdown list/the source code in the data validation hadn't updated to include the added row.
I then unprotected the first worksheet (with the table).
When I added a row to the table it worked. A new row added automatically when I tabbed through the table and it adopted the characteristics/formatting of the table (as did the individual cells). But on the second worksheet the test name wasn't available in the dropdown list/the source code in the data validation didn't update to include the new row.
That's why I had it set up like this:
500 rows in the table with the data validation source code being:
=Tables1!$A$2:$A$500
Every time the end user fills in a blank row the new name is automatically available in the dropdown list. They don't have to unprotect the sheet and they don't have to change the source code (at least until they get to 500). 😉
Is there a way to set it up so that:
A) the end user can add a row to the table (Table name is EE_DB) without having to unprotect the worksheet and
B) the "source" in the data validation for the dropdown list on the other worksheet updates automatically (so that the end user doesn't have to unprotect it and change the source code themselves)?
C) have the blank rows not show in the dropdown list or if they have to show then force the dropdown list to display from the first row (which would be row 2 of the table because there's a header)?
Thank you again so much!!
If data validation list is based on the structured table located in the same sheet of the workbook, it expanded automatically with expanding of the table.
If table is on another sheet of same workbook, that doesn't work. As workaround for the drop-down list you may use formula like
=OFFSET(Sheet2!$A$2,0,0, COUNTA(Sheet2!$A$2:$A$500) )instead of the direct reference
=Sheet2!$A$2:$A$500
- Thank you so much, Sergei! I used the OFFSET one and it worked, thank you so much! 🙂
Have a wonderful day!
DianeHi Diane, glad it works, thank you for the feedback. However, on 365 second option with TOCOL() or FILTER() is more preferrable from performance point of view. OFFSET() shall work for any Excel.
Have a nice day.
- Hi Sergei!
Thank you so much!
I just saw this message from you, I apologize for my delay in responding.
My goal is to have it work for as many versions of Excel as possible even though I'm using 365 to create it.
That being said, is there anything I should be concerned about for folks using 365 if I use OFFSET instead of TOCOL or FILTER? You mentioned a better performance from TOCOL and/or FILTER for 365, does that mean that OFFSET might occasionally not work properly in 365?
Thank you again so much! Have a great weekend!
Diane