Connect several columns from data sheet to another sheet

I have searched all over for this and have seen several references to VLOOKUP and INDEX/MATCH.

From what I have been able to gather is those functions are using some type of extra reference to work.

I have a large data sheet with a lot of columns. I am trying to copy the data from a few specific columns into another sheet and then have columns on the new sheet that act on that data.

How do I have columns duplicated straight over to another sheet with no filtering or summarizing?

Again, I have seen the recommendation for VLOOKUP and IDEX/MATCH. If that is what I am looking for, I may need a little deeper explanation to manipulate the function.

Any help would be greatly appreciated.

Chris

Tags:

Answers

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭

    Hey Chris!

    Can you detail a little bit more what you're trying to do:

    1.) Are you using every single row from your large Data sheet in the new/more slim sheet, or just specific rows?

    2.) Is there some kind of shared, unique reference between the sheets? (e.g. a part number, part title, activity?)

    If you're using all the rows, or you don't have a unique reference that's already on both the sheets - you can link a whole column

    Here's how:

    Step 1.) Create a column you want to link (I named mine "Linked Column" in my example)

    Step 2.) Right click on the TOP Cell in the column you want your data in & select "Link from Cell in Other Sheet"

    Step 3.) Select the COLUMN HEADER for the Column you want to link. ("Task Name" selected here)

    Step 4.) Click "Create Link" - this will pull all the data in that column from the other sheet into your new, lean sheet. You can repeat this for multiple columns.


    Let me know if this meets your needs, or if you only have specific rows you want to pull over / have a unique identifier on both sheets - in which case you'll want to use Index/Match.

    Let me know if you need any other help as well!


    -Jon Mark

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Chood

    I hope you're well and safe!

    I'd recommend connecting the sheet with INDEX/MATCH in combination with an Auto Numbering Column.

    Something like this.

    =
    INDEX({ColumnWithTheValueYouWantToShow}; 
    MATCH(CellThatHaveTheValueToMatch@row; {ColumnWithTheValueToMatchAgainsTheCell}; 0))
    

    Depending on your country/region, you'll need to exchange the comma for a period and the semi-colon for a comma.

    Make sense?

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Chood
    Chood ✭✭

    In my main sheet, I have a lot of columns for a lot of different irrelevant information for what I am trying to do.

    I am trying to bring in just a few columns to the clean sheet to perform calculations and automations on.

    For instance, I want the Technician name, Coordinators name, activity performed and product activity was performed on brought over to the new sheet.

    Once in the new sheet, I want to populate an expected revenue column that will be determined by automations based on activity and product. I can do this, but I am having a hard time grasping the concept of syntax for the functions to copy the data to the revenue sheet in real time.

    The first recommendation above works well, but it seems to only apply to what is already in the sheet. When new rows are added to the doner sheet, it does not get carried over to the second sheet.

    I tried understanding the syntax of the second suggestion. I ALMOST get it, but am still struggling.

    Below is what I have with screen captures. If I can get the syntax as it applies to my situation, My light bulb may come on and I will be off to the races.

    See Below:

    The column I want the data to come from is called Assigned Tech and it lives on the Coordination Sheet (Source Sheet).

    When I select this column after being asked to reference another sheet in the formula it appears as:

    Coordination Sheet Range 1


    The destination sheet I want the data to appear in is called Technician Revenue Sheet.

    The column I want the data to appear in is called:

    Technician Name

    The data will be entered into the Coordination Sheet (Source Sheet) and certain columns need to be populated on the Technician Revenue Sheet (Destination Sheet) as they are entered into the Coordination Sheet. It just needs to be 1 for 1 copy as it happens.

    I tried the formula above:

    =
    INDEX({ColumnWithTheValueYouWantToShow}; 
    MATCH(CellThatHaveTheValueToMatch@row; {ColumnWithTheValueToMatchAgainsTheCell}; 0))
    

    But must be confused on the syntax or which Column or Cell name to put where.

    I am sorry to be slow on the uptake here.

    Chris

  • heyjay
    heyjay ✭✭✭✭✭
    edited 05/05/22
    1. replace ; with ,
    2. =INDEX(result column,MATCH(reference cell,reference column,0)


    Other reference: https://help.smartsheet.com/function/index

    ...