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

John Kennedy

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?

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?

John Kennedy

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).

Shot1.PNG

Shot2.PNG

Hope this makes sense. Thanks

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.

John Kennedy

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

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?

John Kennedy

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

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?

John Kennedy

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

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