Forum Discussion

JustLearningFormulas's avatar
JustLearningFormulas
Copper Contributor
Jul 03, 2024

Populating columns with rows from another sheet

I have a Excel document with 2 tabs. The first one is a "partslist". The second tab is automatically pulled from company data and refreshed weekly,"Facility".(this cannot be edited in any way)


What I'm trying to do is have Fac ID column in the Facility tab automatically generate into the partslist table on the Partslist tab. We add facility all the time so manually adding wouldn't be ideal.

 

Fac ID Column(Facility tab) -> Spareparts(tab) =generates a column in partslist table, that will automatically add a new column if a new Fac ID is added.

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 07, 2024

    JustLearningFormulas 

    To automatically populate a column in your "Partslist" tab based on the "Fac ID" column from the "Facility" tab in Excel, you can use a formula that dynamically pulls data from one sheet to another. Here’s how you can achieve this:

    Step-by-Step Solution:

    1. Identify the Range for Fac ID in Facility Tab:
      • Assume your "Facility" tab has the Fac ID column in column A (starting from A2, since A1 might be the header).
    2. Inserting the Formula in Partslist Tab:
      • In the "Partslist" tab, determine where you want the Fac ID column to start. Let's assume you want this in column B starting from B2 (assuming B1 is the header).
    3. Using INDEX-MATCH Formula:
      • To dynamically pull Fac IDs from the "Facility" tab into the "Partslist" tab, you can use the INDEX-MATCH combination. This formula will retrieve Fac IDs from the "Facility" tab based on their position.

    Example Formula:

    In cell B2 of your "Partslist" tab, enter the following formula:

    =IFERROR(INDEX(Facility!A:A, MATCH(1, (Facility!A:A<>"")*(COUNTIF($B$1:B1, Facility!A:A)=0), 0)), "")

     

    Explanation of the Formula:

    • INDEX(Facility!A

    , MATCH(...)): This part of the formula retrieves the Fac ID from column A of the "Facility" tab. MATCH finds the first occurrence of a Fac ID that hasn't been added yet (using COUNTIF to check uniqueness).

    • (Facility!A

    <>"")*(COUNTIF($B$1

    , Facility!A

    )=0): This constructs an array of 1s and 0s. It checks:

      • If cells in column A of "Facility" are not empty (Facility!A:A<>"").
      • If the Fac ID has not already been added to the list in the "Partslist" tab (COUNTIF($B$1:B1, Facility!A:A)=0).
    • IFERROR(..., ""): This ensures that if no more unique Fac IDs are found, it returns a blank cell instead of an error.

    Adjustments:

    • Range Adjustment: If your actual data starts from a different row or column, adjust the formula ranges accordingly (Facility!A:A should match the actual column where your Fac IDs are, and $B$1:B1 should match the column where you start placing the formula).
    • Auto-Expansion: As you add new Fac IDs to the "Facility" tab, the formula in the "Partslist" tab will automatically populate new rows.

    Notes:

    • Ensure that your references (Facility!A:A and $B$1:B1) are adjusted to match your actual data structure.
    • This method assumes that "Facility" tab data starts from row 1 (with headers) and that Fac IDs are in column A.

    By using this approach, you can dynamically populate the Fac ID column in your "Partslist" tab based on the data in the "Facility" tab, and it will automatically update as new Fac IDs are added or existing ones are modified. Texts, steps and functions were created using AI. The formulas are untested, so make a backup of your files beforehand.

     

    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