I need an excel formula that automatically updates data from the most recently created tab of another spreadsheet even after adding or deleting rows from the other spreadsheet.

Question

I need a formula for cells in spreadsheet B-column A that will correctly reference cells from the most recently added tab in spreadsheet A-column A, even after adding or deleting rows in spreadsheet A.

Answer 1

Hello C_S,

Thank you for reaching out to Microsoft Q&A.

Regarding your inquiry about automatically pulling data from the most recently created tab of another spreadsheet—while maintaining accurate references even after rows are added or deleted—please note that standard Excel formulas such as VLOOKUP, INDEX, or INDIRECT are not capable of dynamically identifying the most recently created worksheet or adapting to structural changes in external workbooks.

Although Excel does not natively support referencing specific fields from dynamically changing sheets, tools like Power Query or Power Automate can help you build a more robust and flexible solution. These tools allow you to extract only the necessary fields, improving both the efficiency and usability of your workbook.

That said, due to limitations in accessing your internal systems, our ability to provide hands-on setup assistance is restricted. For more advanced or customized support, we recommend posting your question in the Power Platform Community, where experienced users and Microsoft experts can offer more targeted guidance.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

You need a value somewhere in row 5 of spreadsheet A so that spreadsheet B can find cell A5 no matter how it moves up or down due to inserts and deletions. If there is not already such a value in the row, put a sentinel value in cell BB5 (or any other cell in row 5 sufficiently far to the right so it is out of the way. You can then replace the hardcoded "'!A5" with "'!A"&MATCH(BB:BB,value,0)