Home > microsoft excel > Formula to display text from multiple cells from one sheet in another

# Formula to display text from multiple cells from one sheet in another

January 19Hits:1

I have a list of sales reps and the names of the customers they've sold product to, on one sheet. I have tried VLOOKUP, but it will only return one set of text from the cells. I would like to know, please, if there is another formula/function that can reference the name of the sales rep to display the text contents multiple cells containing the customer's names on separate sheets so that: On Sheet 1:

Customer  Customer Sales Rep          1st Name  Last Name Bob Smith           Randy     Quaid Bob Smith           Tom       Cruise Bob Smith           Sarah     Parker Bill Jones          James     Franco Bill Jones          Clint     Eastwood Jane Doe            Brad      Pitt Jane Doe            Bruce     Willis

And then, using the formula/function on Sheet 2:

Customer  Customer                           Sales Rep          1st Name  Last Name        Bob Smith           Randy     Quaid                               Tom       Cruise                     Sarah     Parker

...on Sheet 3:

Customer  Customer                           Sales Rep          1st Name  Last Name Bill Jones          James     Franco                     Clint     Eastwood

...on Sheet 4:

Customer  Customer                           Sales Rep          1st Name  Last Name      Jane Doe            Brad      Pitt                     Bruce     Willis

As I stated, when I used VLOOKUP, it only returns ONE of the customer's names, not all 2 or 3, so I am at a standstill. I apologize if there is a basic answer to this that I am over looking, and I appreciate all the help I can get. Thank you!

A pivot table can do this, albeit a bit clunkily. Select your data and column headers (only one row for headers), Insert | Pivot table. Drag each element into the "Row headers" area. You should now see something resembling your desired result. It will be messy, but can be cleaned up by turning off subtotals on each of the header fields. Last, replicate the sheet with the pivot table and filter the Sales Rep field.

## Related Articles

• ### Formula to display text from multiple cells from one sheet in anotherJanuary 19

I have a list of sales reps and the names of the customers they've sold product to, on one sheet. I have tried VLOOKUP, but it will only return one set of text from the cells. I would like to know, please, if there is another formula/function that ca

• ### Return text from multiple cells on another sheet with linefeed if dates matchFebruary 25

I have an example here of my spreadsheet. Basically my main page has a drop-down (via data validation) where you can toggle through any month and it responds by showing data for that month. What I want to do is be able to type a note for a specific d

• ### How do I pull text from multiple cells in a google spreadsheet to website each in a different p tag?January 24

I am working on a webpage for a client. One of the requirements is that they are able to easily edit the pictures along with the items' descriptions on their gallery pages. I have a google sheet with item descriptions. I would like to pull these desc

• ### Implementing 3 different Macros on Multiple Cells in a Sheet by SelectionSeptember 18

I am a complete noob at both excel and Vba and am trying to come up with code that can give me a timer and a cell color change that occurs when I click on my three buttons StartBtn, StopBtn and ResetBtn. Right now I got the code to work for a single

• ### Enter different text in multiple cells in a row based on one cell's valueJanuary 5

Totally new to this but need some help. Any advice appreciated. I was wondering if it is possible to write some kind of formula that would automatically fill about 12 cells in a single row based on the value of the 1st cell. Eg: If A1=1, then B1=Pass

• ### Combine text from multiple cells into a single cell forming a sentence?October 17

I would like to combine text from Cell A2, B2, C2-I2, J2, into a sentence in E2. Same thing would continue down in Row 3, 4, 5, etc. I would want the sentence to end up being "[A2] can be bought from [B2], by [C2-I2] after you do the quest: "[J2

• ### How do I delete specific text in multiple cells at once?July 7

I have a massive list of email addresses; but just to make things simple i am dealing with 3 - 10 email addresses that are typed up in 1 cell and i cant seem to MASS delete specific ones. Is there any way I can delete a specific email address from th

• ### Formula for getting format of a cell in Google Sheets October 8

Is it possible to reference a cell in Google Spreadsheets so that cell where it gets displayed also displays it using the same text and cell colour formatting? =A1 Will only reference dell's value. But if that particular cell has red background and w

• ### Copy text from "joined cell" to another sheet's smaller joined cellApril 3

Normally, text appears in the cell when it contains a reference to another (=Sheet1!A1 will show the contents of sheet A1) However, if both target and destination are joined cells, and the target is larger then the destination, then the only text tha

• ### How do I reference multiple cells in one sheet in one cell in another sheet?April 15

I am using Google Sheets and I have two sheets in one document (SHEET1 and SHEET2). I want to be able to use a formula that allows me to: If cell A1, B1, D1 and F1 in SHEET1 have an s in them Then cell A1 in SHEET2 automatically inputs an s in it Can

• ### Excel display time NOW in cells automatically on sheet changeJanuary 8

In my A1,A2,...,A10 cells i have this code =IF(B1>0,NOW(),0) So if B1>0 A1 equals to Date time now and all this for B2 and A2 B3 and A3 and so on. But the problem is that if i change for example B4 all cells from A1 to An automatically change to the

• ### How to add space inside multiple cells in Excel 2010May 26

I would like quickly insert or add space to the beginning of text in multiple cells (rows) simultaneously. Is there any formula to do so? --------------Solutions------------- If VBA script is an option then try this. Create a button and assign this t

• ### Conditional formatting of an entire row based on multiple cellsSeptember 25

I'm trying to make a conditional formatting rule in Google Spreadsheet that would highlight a row green based on text in multiple cells. If cells L2, M2, and N2 = APPROVED; and cell O2 = X; then row 2 = highlighted green. --------------Solutions-----

• ### Excel formula to display a string of text and numbersFebruary 7

I need one column to show the following text: prxyz,(data1 here),0699,ABCD(data2 here),xxx data1 is in column A and data2 is in column B. the format of data1 needs to be 5 numbers (sometimes beginning with zeros). for data2, they need to type in data

• ### MS excell =IF formula across multiple cellsSeptember 19

I want to create an IF formula across E1, F1, G1 and H1. If true, it should display "Commence Fire Drill," that is situated on the data sheet C5 (E3:H3=Data!A1) and if False "All in Order" that is situated on the data sheet C6 in G1. I

• ### Split A text-style cell to multiple cells by a fixed widthJune 6

Sample Cell 1 Cell["Much of point set topology consists in developing a convenient language to talk about when various points in a space are near to one another and about the notion of continuity. The key is that the same definitions can be applied t

• ### How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)?November 21

How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)? Excel File Origin I have an Excel file that was originally a CSV file. At this time I don't have access to the original CSV file. When the CSV file was o

• ### How can I use text from a cell as part of a formula?February 17

I do not want to concatenate a string or show the value like "Some Text " & C12. I need to use the text in a cell as part of the formula in another cell. For Example: I have a cell containing a date 17.02.2014. I need to use that date in a f

• ### Excel cells- show displayed text instead of value in Data Validation listAugust 4

I read this topic: Excel cells - show text different to the actual value?, and now I'm trying to use it as a Data Validation list in Excel. But when I choose for the list those cells, when I choose one of them it shows me the value data and not the d

• ### Why is pasting text into Excel splitting it into multiple cells?October 1

When I am copying partial text from one cell and pasting into another what I am pasting is separating into multiple cells. Example: Copying "June 15th" from a cell containing "Little Timmy's Birthday is June 15th", it pastes into two c