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
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
Have a wonderful day!
Diane
Hi 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!
DianeOFFSET() itself is quite fast function and it works on all versions of Excel. The problem is they are recalculated each time you do any calculation in the workbook. In simplest case, entering into any cell =1+2 forces recalculation of all OFFSET().
Thus all depends on our file, is that critical or not.