Formula To Loop and Copy Without VBA

Question

Starting in cell A5, I have a list of airport codes, where each week, the last row will change.

I need to feed each code into a data validation cell on a different sheet, which will than generate a range of start and end dates in two cells that appear to the right of the data validation.

Is there a formula that would allow me to loop through each value and pull the two dates without VBA

Answer 1

Dear ExcelClass,

Good day to you and thanks for your post in Microsoft Community. I'm sorry for the inconvenience you've encountered.

I apologized for my misunderstanding about your requirements. Please forgive my mistake.

• Regarding your issue, I investigated it and see that Power Query can help it. Please help me to check if it meets your requirements.
  Sheet 1 has table "tblCalc", sheet 2 has table "tblCodes"
  • 
• Load 2 tables into Power Query
  • In ribbon Data > Get Data > From Other Sources > From Table/Range
  • Select table "tblCalc" > Power Query Editor open query as name "tblCalc" > Close & Load To... > Only Create Connection > OK
  • Select table "tblCodes" > Power Query Editor open query as name "tblCodes" > Close & Load To... > Only Create Connection > OK
    • 
    • 
    • 
    • 
• Merge Queries
  • In ribbon Data > Get Data > Combine Queries > Merge
    • 
  • Primary table: tblCodes. Secondary table: tblCalc. Select first column of each table.
  • Join kind: Left Outer (all from first, matching from second)
    • 
  • Click OK > Power Query Editor appear with new query "Merge1"
  • Delete 2 column as below
    • 
  • Then Click icon narrow left/right on tblCalc and setting as below:
    • 
  • Now Merge1 has 3 columns like this
    • 
• Load result to Sheet2
  • Home > Close & Load To...
    • 
    • 
    • 
• Now you will have a new table that will change when cell Validation change (press Refresh all). Old table in Sheet2 will be become sample, you can move it to the end of sheet to make the sheet clearly

Once again, I sincerely apologize for the unpleasant experience. Thank you for your patience and kindness. I look forward to your information and if you have any questions, please feel free to reach out to me - I’ll be happy to assist you. Wishing you a wonderful day. 

Best regards,

Daniel Vo - MSFT | Microsoft Community Support Specialist.

Answer 2

Dear ExcelClass,

Good day to you and thanks for your post in Microsoft Community. I'm sorry for the inconvenience you've encountered.

I hope you had a wonderful weekend with your family and friends. Could you please take some time to review my latest response? Please give me some feedback, I will take all my effort to improve your problem.

Once again, I sincerely apologize for the unpleasant experience. Thank you for your patience and kindness. I look forward to your information and if you have any questions, please feel free to reach out to me - I’ll be happy to assist you. Wishing you a wonderful day. 

Best regards,

Daniel Vo - MSFT | Microsoft Community Support Specialist.