Changing row number dynamically in link to another file

[AntBlabby8]

I have a group of Excel templates that I use repeatedly to upload to another system. There are about 60 links to another file, a source file, each in different columns but all in the same row or rows; sometimes it involves combining data from multiple rows so I have different templates, depending on the number of transactions (rows) I need to combine into a single formula to enter in each cell by links.

The source document is accessible to me but updated by another department by adding new transactions on a new row. As I'm informed that data for a new transaction has been added, I have to relink for the row or row numbers and do an upload. So many times a week.

I envisioned a way where I could concatenate the file name I'm linking to, with a number for the new row number. Just open the template, have a cell for the row number, and voila, the template would update all links.

How do I do that??

THANKS!!

 

[AlphaFrog]

Take a look at the INDIRECT function. It takes a text string and converts it to a range reference.

INDIRECT function - Microsoft Support

How to use the INDIRECT function in Excel to change the reference to a cell within a formula without changing the formula itself.

support.microsoft.com