Combining cells from one sheet into a single cell on another sheet

I have a master spreadsheet with many columns (sheet1). From that, I want to create a second sheet with fewer columns (sheet2). The first column in sheet1 is 3 cells combined (=[Column1]1 + "-" + [Column2]1 + "-" + [Column3]1). In sheet2 I only need the info from Column2 and Column3. Is there any way to do this from a single reference?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots of the two sheets with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed? Maybe include an example that is manually entered to show exactly how you want the end result to look?

  • So I have the sheet in the first screen shot (Shot1). The Lookup column is a combination of the other 4 columns (Technical Category through Activity) and is achieved with this formula - "=[Technical Category]1 + "-" + [Functional Area]1 + "-" + [Tool Name]1 + "-" + Activity1".

    I have a second sheet that I want to be able to take columns Functional Area, Tool Name, and Activity from the first sheet and use that. I have been able to take those 3 columns and linked to them, using those to create my cell as above but I then need to hide those 3 columns where I would rather make the straight reference into the Task column from the second screen shot (Shot2).

    Hope this makes sense. Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What do you mean by "linked"? Are you using a formula with cross sheet references or are you using cell linking? What is the end goal for this data?


    Having additional columns is going to end up being the easiest way to go regardless. If I am correctly following what you are trying to do, you very well could end up using a rather complex formula.

  • The formula for Shot1 is "=[Technical Category]1 + "-" + [Functional Area]1 + "-" + [Tool Name]1 + "-" + Activity1" to populate the Lookup column. My end goal would be something like "=Sheet1:[Functual Category]1 + "-" + Sheet1:[Tool Name]1 + "-" + Sheet1:Activity1" to populate the Task column.

    By linked, I mean I used the link tool in Sheet2 to link the 3 required columns in Sheet1 to the end of Sheet2. I then used a formula like the above to populate the Task column with the contents of those 3 linked columns and hid the columns (it is these steps that I am trying to avoid).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    And you are doing this for every row? Do you have any uniquely identifying information for each row such as a row number or a column that has data that will not be duplicated in any other row?

  • We are doing it for every row. The original sheet is not mine (I don't have edit rights) so I am trying to use that data for my purposes.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you have any uniquely identifying information for each row such as a row number or a column that has data that will not be duplicated in any other row?

  • Yes. Here are the column titles for Sheet1:

    Lookup:Technical Category(1):Functional Area(2):Tool Name(3):Activity(4):Multiplier:Build-Infra Hours:Build-Tooling Hrs:Notes

    NOTE: (1), (2), (3), and (4) are used to populate Lookup in Sheet1. I want to use 2, 3, and 4 to populate Task in Sheet2

    Column titles for Sheet2:

    Task (taken from sheet1 is goal):Story:Multiplier(#):Build-Infra hrs(#):Build-Tooling hrs(#):Story Points:Notes(H):Functional Area(H):Tool Name(H):Activity(H)

    NOTE: (#) - Copied from Sheet 1 for reference and likely edited to not be copies. (H) - Copied from Sheet1 currently to populate Task but that is what I want to replace - Hidden cells

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So where is the data that is unique to each row that can be used to match against on both sheets?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!