Forum Discussion

Wiljunow's avatar
Wiljunow
Copper Contributor
Jul 20, 2024

How to associate manually filled cells with cells linked to other excel files?

Hello everyone,

First of all, I'd like to apologize if I made any mistakes. English isn't my first language.

I have encountered a problem at work and I wonder if you could help me.

I have linked some cells to other Excel files so they can fill themselves according to the cells from those files.

Furthermore, I manually filled some other cells on each line corresponding to the imported cells.

The problem arises when I update the files from which the data is imported.

Indeed, when lines are added to those files, they are also added to my main Excel file (which is good, that's what I want), but when those lines are added, my manually written data doesn't move with the cells they were next to originally. As a result, they appear next to cells they shouldn't be next to. Basically, my whole column of manually written data becomes incorrect.

What can I do to ensure those manually written cells stay with the imported cells so they move together if more lines are inserted in between?

Thank you for your help,

Wiljunow.

Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 21, 2024

    Wiljunow 

    To ensure that manually filled cells stay associated with the cells linked to other Excel files, you can use a combination of techniques that Excel provides for managing data and maintaining integrity across updates. Here is a step-by-step approach:

    1. Use Structured References in Tables

    1. Convert your data range to a Table:
      • Select your entire data range including the manually filled cells and the cells linked to other Excel files.
      • Go to the Insert tab and click Table. Ensure the "My table has headers" option is checked if your data has headers.
      • This will create a structured reference system that helps maintain the integrity of your data.
    2. Linking Data from External Files:
      • When you link data from external files into your Table, ensure that these links are part of the Table structure. This way, any insertion or deletion of rows in the linked data will automatically adjust within the Table.

    2. Use Formulas to Dynamically Reference Data

    If you can't or don't want to use Tables, you can use formulas to ensure that the data stays synchronized.

    1. Create Named Ranges or Dynamic Ranges:
      • Define named ranges or use dynamic ranges for your data. This way, when new data is added, the named ranges will automatically adjust.
    2. Index-Match or VLOOKUP:
      • Instead of linking directly to cells, use formulas like INDEX-MATCH or VLOOKUP to fetch data from the external files. This ensures that the manually entered data can be referenced correctly even if rows are added.
      • For example, if your manually entered data is in column B and the linked data is in column A, you can use:

    =INDEX(ExternalFileRange, MATCH($A2, ExternalFileLookupColumn, 0))

      • This formula will look up the value in column A and fetch the corresponding data from the external file, ensuring alignment.

    3. Use Helper Columns

    You can create a helper column that combines the linked data and manually filled data in such a way that it maintains its integrity.

    1. Helper Column:
      • Insert a helper column that concatenates the unique identifier (like a row number or ID) with the manually entered data.
      • For example:

    =A2 & "-" & B2

      • Use this helper column to maintain the association.

         2. Use Helper Column in Formulas:

      • Adjust your formulas to reference the helper column to ensure data integrity.

    Example Steps

    Here’s an example of using a Table with formulas:

    1. Convert your data range to a Table:
      • Select the range and choose Insert > Table.
    2. Link external data:
      • Assuming your external data is in a file named DataSource.xlsx and you want to link to cells A1

    in Sheet1, you can use:

    =[DataSource.xlsx]Sheet1!$A$1:$A$10

        3. Use structured references to link the data:

      • In the Table, you can use formulas like:

    =VLOOKUP([@YourIdentifier], ExternalDataRange, ColumnIndex, FALSE)

      • This ensures the data remains linked correctly even when rows are added.

    By organizing your data using these methods, you can ensure that manually filled cells stay correctly associated with their corresponding linked cells even when new data is added to the source files. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources