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.

For your issue, I would like to provide a way that can help you, please help me to check it in below:

• I have a sample table include code, start date and end date.

• In A17 when insert code "abc" it will return start date and end date of it

• My code: Start date=XLOOKUP(A17, tblAirports[code], tblAirports[start date], "Not found"). End date=XLOOKUP(A17, tblAirports[code], tblAirports[end date], "Not found")

My excel version is

If you cannot execute like me, please give me a screenshot your Excel version.

Please help me to check if my instruction can meet your requirement. 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 Karishma Behera1,

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

If your table has 2 code same value, I recommend you use function "FILTER"

• My formula: =FILTER(tblAirports[start date], tblAirports[code]=E4, "Not found")

Please help me to check if my instruction can meet your requirement. 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 3

Hello,

Here is a picture of a workbook

Sheet 1, Sheet 2

Pull the letters from sheet 2, column A, substitute those values into the validation cell, then read the resulting formulaic dates from sheet 1 back to sheet 2, to the right of the code

Answer 4

Dear ExcelClass,

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

Thank you for your provided information. Regarding from that, I would like to upload new instructions for your issue:

1. In sheet 1, it has a table of data like below
   • 
2. Next step, a define name will be set up for column "code" of table
   • Choose first value of column "code" (A5) then press ctrl + shift + arrow down. It will select all data of column "code"
   • In Formulas tab > Define Name
     • 
   • And set up like this
     • 
   • Now, you will have ListCodes to represent for data of column "code"
3. Create dropdown (data validation)
   • In sheet 2, you a cell you want to have data drop down
   • In tab Data > Data Validation
     • 
   • In tab Settings, set up as image then click OK
     • 
   • Now, when click on that cell, you will see dropdown list all value on column "code". And then when add/erase/expand line in Table, list will be updated automatically.
     • 
     • 

Please help me to check if my instruction can meet your requirement. 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 5

Thanks Daniel, but that's completely the wrong answer and not relevant to the question or my picture. Please stop while you're ahead