I want to extend related tables that have related formulas

File : Updating Dependent Lists.xlsm

Okay, so on the "Data" worksheet I have 2 tables: Departures List and Unique List. Every time I input new data using the form I made on Sheet 1, it is automatically recorded on the Departures List whenever I click the submit button. The Unique List in turn lists all data WITHOUT repetitions. Why? Because this data is used on the 3rd sheet. On the "Values" data validation drop down menu located in the "Report" worksheet, the choices that are present in the Unique List are listed. Once a choice has been made, it then displays all data related to the choice to the right (highlighted in blue)

What I want to do is to extend the Departures List, Unique List in the Data worksheet and the Display list on the Report worksheet 500 Cells down because as what it is now, the data it can record is limited. Simply dragging down the cells did not work for me. I have noticed that the cells in the Unique List are enclosed in {} which I have no idea of. I just want to extend the range of all tables atleast 500 cells below but whenever I drag or modify the formula, the data on the Unique List returns "0" and I dont know why..

addtl info: there is a defined name "listvalues" which is offset of the unique list so whenever a new entry is added, the data validation drop down list is automatically updated in the Report worksheet.

I have tried modifying the formula, recreating the file, dragging and such and none has worked. Ive been on this for half the day now trying to figure out how to go about this which has led me to this forum. Hope you guys can help!

I believe that the attached file will meet your specifications. The tables are now extended to 500 rows by changing the formulas as in:
Original

Please Login or Register  to view this content.

Updated

Please Login or Register  to view this content.

Also starting the updated formula with IF(...=0,""...) eliminates the zeros.

Copy of Updating Dependent Lists.xlsm

Let me know if you have any questions.