I created a spreadsheet a few months ago and have a drop down list where i typed in a formula to populate the list from a range in another sheet. Now i have added more items to the range on the other sheet but i cannot edit the source for the drop down list to include it.
When i open the data validation box and click on the source box to edit it, nothing happens. I can highlight the contents but i cannot delete or add any characters for some reason.
This is the existing formula:
=IFS(B2=1,SectionProperties!B4:B18,B2=2,SectionProperties!B19:**B141**,B2=3,SectionProperties!B144:B165,B2=4,SectionProperties!B166:B285,B2=5,SectionProperties!B286:B298,B2=6,SectionProperties!B299:B310,B2=7,SectionProperties!B324:B352,B2=8,SectionProperties!B397:B505,B2=9,SectionProperties!B506:B519,B2=10,SectionProperties!B520:B550,B2=11,SectionProperties!B552:B683,B2=12,SectionProperties!B684:B709)
and this is what i need to change it to:
=IFS(B2=1,SectionProperties!B4:B18,B2=2,SectionProperties!B19:**B143**,B2=3,SectionProperties!B144:B165,B2=4,SectionProperties!B166:B285,B2=5,SectionProperties!B286:B298,B2=6,SectionProperties!B299:B310,B2=7,SectionProperties!B324:B352,B2=8,SectionProperties!B397:B505,B2=9,SectionProperties!B506:B519,B2=10,SectionProperties!B520:B550,B2=11,SectionProperties!B552:B683,B2=12,SectionProperties!B684:B709)
I know its probably a terrible way to write the formula, but it works. Please kindly do not rip me apart for bad formulas, though suggestions for improvement would be much appreciated. I just need to change one little number and cant figure out how.
Thanks for your help.
