Manipulating Text between sheets, in a workbook

What I am trying to do is set up a cell where I can type in a name, and then 
have that name collect under an appropriate field on another worksheet.  Then 
when I type in a different name, into the same cell, it collects that name 
UNDER the first name on the other worksheet.
0
6/14/2005 7:04:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
591 Views

Similar Articles

[PageSpeed] 11

Would require event code behind the worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   On Error GoTo stoppit
   Application.EnableEvents = False
   If Target.Address = "$A$1" And Target.Value <> "" Then
   Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp) _
  .Offset(1, 0).Value = Target.Value
 End If
stoppit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Paste the above code into that sheet module.

When you enter a name in A1 that name will be copied to Sheet2 B2 then next
time you type a name in A1 that name will be copied to Sheet2 B3 and on and
on.

Adjust to suit your fields and columns.


Gord Dibben Excel MVP

On Tue, 14 Jun 2005 12:04:04 -0700, "mdjennings"
<mdjennings@discussions.microsoft.com> wrote:

>What I am trying to do is set up a cell where I can type in a name, and then 
>have that name collect under an appropriate field on another worksheet.  Then 
>when I type in a different name, into the same cell, it collects that name 
>UNDER the first name on the other worksheet.

0
Gord
6/14/2005 9:08:31 PM
This isn't quite the same thing, but you may want to try Data|Form.

You can always sort by the names later if that's a problem.

mdjennings wrote:
> 
> What I am trying to do is set up a cell where I can type in a name, and then
> have that name collect under an appropriate field on another worksheet.  Then
> when I type in a different name, into the same cell, it collects that name
> UNDER the first name on the other worksheet.

-- 

Dave Peterson
0
ec357201 (5290)
6/14/2005 9:20:18 PM
Reply:

Similar Artilces:

connect text box toolbar is grayed out.
The "connect text box" toolbar is grayed out. How do get it to work? I've tried clicking within the overfloweing text area that I want to flow into another textbox, and I've clicked on the toolbar itself, but nothing works. Any help will be appreciated. I have Publisher 2003. You can only link to an empty text box. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Toody" <Toody@discussions.microsoft.com> wrote in message news:D8082162-7C67-41CB-BC8D-9450BF08F721@microsoft.com... > The "co...

Totaling text cells
Is it possible to autosum the total of cells that contain only text, in a worksheet that contains cells with only text and cells with only numbers? Do you mean that you want to *COUNT* cells that contain a specific TEXT entry? Try something like this... =COUNTIF(A2:A100,"Yes") That will count cells in the range A2:A100 that contain Yes. -- Biff Microsoft Excel MVP "Malcolm" <Malcolm@discussions.microsoft.com> wrote in message news:98256116-B5D1-42EA-A087-D1CD9EA4A591@microsoft.com... > Is it possible to autosum the total of cells that c...

Font manipulation
Hello. I'm curious I can change apperance in chat window. Like the background of the tab of my messages and different background of the tab of the different user. I don't mean the background of the text. And is there any way of increace the spacing of lines between them? The lines are really near... For more understanding: I wish to control it somewhere with like a css command "Padding" or "margin". Like ..text_me { font color: gold; font size: 1.5em; padding: 4px; } ..text_others { font color: black; padding: 4px } I think y...

Separating Text strings.
Is it possible to separate a name in one cell such as John Smith in A1 to John in cell B1 and Smith in cell C1. I read that by entering =LEFT(A1,FIND("",A1)) into B1 and =MID(A1,FIND("",A1)+1,LEN(A1)) into C1 that would do it. However that returns �J� in cell B1 and �ohn Smith� in C1 Specifying the number of characters for the first string would not work on a list of names were the first string varies in length, so you need a formula that takes everything before the first space as the first string. Is it possible? -- Quaisne -------------------------------------------...

Print sheets based on cell value
I have a workbook of about 30 sheets. Beginning with the 3rd sheet, each sheet has a date in cell K4. I wish to print just the sheets for which the month in K4 equals certain values. So far I have this: Sub PrintSheets() Dim MonthDue As Integer ThisWorkbook.Activate For Each sh In ThisWorkbook.Sheets If sh.Index > 2 Then sh.Select 'changing the activesheet MonthDue = Month(K4) Select Case MonthDue Case 4 ActiveSheet.PrintOut Case 12 ActiveSheet.PrintOut End Select End If...

VBA Import from Access using ODBC
I am trying to import from Access using ODBC - then I want to basically concatenate two of the columns. If I run my macro it actually runs the concatenate part first, then brings in the odbc information. If I put a pause or step through it - it works no problem. Code is provided.. What am I doing wrong? Thanks in advance... dc With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & fileandpath & ";DefaultDir=" & spath & "; DriverId=281;FIL=MS Access;MaxBufferSize =2048; PageTimeout =5;")), Dest...

How to print CD/DVD labels on standard two-per-sheet paper?
Every time I download templates to print CD/DVD labels, I see only one label on a small sheet of paper. When I go to the office supply store, I only have the option of buying paper with the standard two-label design. How do I create and edit CD labels where I can work with both onscreen and then print both at once (different top from bottom) in such a way that everything is aligned with the cutout sticker labels on the page? Anytime you need to edit both halves of your label you need to setup the page manually. Most CD/DVD templates will print two to a page. Select the label you want...

Outlook adding txt attachment to plain text messages
I'm using Outlook 2000. Recently, it started adding a text file (*.txt) containing the text of the email. It only does it when the incoming email is in 'plain text' format. If it is an 'html' email, it doesn't attach anything. I haven't changed any of my settings ... I can't find anywhere to turn it off. I'm not sure if this is the result of a Windows update or not. Does anyone have any ideas? Thanks in advance. Hello, the german MVP have answered that question in his=20 Commnunities. =B2It is a bug and it will be gone after a=20 time." >---...

Hour manipulation
i have a file download that shows hours worked for employees in th following format 72:09:50(hh:mm:ss). The problem is this, the data i displayed in that format but in the formula bar it shows the data a 1/3/1900 12:09:50 AM. Because of this, I can not manipulate the data How can I, in essence, make the data a string or some state in whic it is not recognized as a date and time. I have tried copying the dat and pasting it as a value, one hundred different formatting tries, an everything else I can think of. pls hl -- Message posted from http://www.ExcelForum.com Redwings, this will tur...

Issues Locking A Sheet
I am trying to lock a sheet so that when the form is emailed the person is not able to make any changes. I am selecting the cells I want locked, protecting the sheet, setting a password, but when I email the sheet the person is still able to make changes. What am I doing incorrectly? Nevermind, I figured out my issue. Thanks this site is always so helpful in all my other issues I have come across! >-----Original Message----- >I am trying to lock a sheet so that when the form is >emailed the person is not able to make any changes. I am >selecting the cells I want loc...

Any way to force European format dates in sheets?
Is there a way to configure Excel (Office 2000) to always display dates in Euro format instead of US? It currently accepts entries in Euro format (dd/mm/yy) but always displays them in sheets in US format (mm/dd/yy), which is kinda confusing. I can't see a configure option covering this - am I missing something? Cheers, -Neil F. Suspect this is down to your PCs settings - check control panel - Regional Options. Are you sure its accepting Euro dates, and not just those that can be mis-interpreted as US and leaving others just as text? Thanks for that, Ben. I've checked t...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Go to Tools > AutoCorrect Options (XL2002) and insert new...

Hiding / Unhiding Sheets
I have created macros to hide and unhide sheets when clicking on a button example - Sheets("Premium Labor Utilization").Visible = Not Sheets("Premium Labor Utilization").Visible I am also assigning goups of sheets to a summary button example Application.ScreenUpdating = False Sheets("Premium Labor Utilization").Visible = Not Sheets("Premium Labor Utilization").Visible Sheets("FMLA Compliance").Visible = Not Sheets("FMLA Compliance").Visible Sheets("FLSA Compliance").Visible = Not Sheets("FLSA ...

Manipulating shapes
I have on a worksheet 3 autoshapes as follows: 1 UpArrow 1 DownArrow 1 LeftToRightArrow What I want to do is show one of these shapes (make it visible) when the value in a particuler cell reaches a certain point e.g. Value < 50 DownArrow Value = 50 LeftToRightArrow Value > 50 UpArrow I want this shape to be linked to a report in word (cell in a table), rather like you would with a chart. When the values in the worksheet change, the chart in the word document is updated and the appropriate 'Arrow' is shown in the corresponging table cell. Any help would be much appreciated....

Excel VBA
Hi, How to import text file to Excel worksheet using VB? Can I use a for menu to execute this process? Please give me some ideas of doing it. have attached a text file for your reference. Thanks! AN -- Message posted from http://www.ExcelForum.com >>How to import text file to Excel worksheet using VB? Record a macro as you do it manually with File, Open. Pick the appropriate file type from the drop down. That will give you a good start. >>Can I use a form menu to execute this process? form menu? No such thing. -- Jim Rech Excel MVP "annsmjarm >" <<an...

Predictive text?
You can create a lookup table through Data, Validation, List. But can you do a similar thing where the lookup list is sorted depending on what you type in? eg: If you start to type "peter" the list shortens to items begining with p when you type "p" and then shortens to items begining with pe when you type "pe" etc. Many thanks Peter Excel will auto complete the word if that same word already exists directly above or below the validation cell in the same column. I would move the source list for the validation cell directly above it, then hide those cells...

Text to Columns?
In my spreadsheet, from cells F1 to CA1, I have a list of titles. At the beginning of each of these titles is the words Out of Stock/. I would like to know if there is an easier way to get rid of these words other than text to columns. Right now, I insert a column next to one with text and use Text to Columns and choose "/" as my separator, and then I have to go back and delete all of the unwanted cells. I didn't mind doing this once, but now that I have to do it weekly, I need to find a faster way. TIA This is what it looks like now. F1 ...

Can text in ListBox and MsgBox be high lighted?
During the execution of a macro I use ListBox and MsgBox. Is it possible to have certain words/text highlighted in Bold and/or Italics? If I have the following text that is displayed in a ListBox:- “The sheet that needs the data to be extracted should be called MASTER DATA.” What I would like is the last two words (i.e. MASTER DATA) to be in bold and Italic and if possible in font Verdana and size 20. Additionally if I have a MsgBox that displays the following text:- "Continuing with Extract. Please select OK" What I would like is the last three words (i.e. Please select OK) ...

named cells not referenced in other sheets
how do i fix this situation? ...

Get sheet by index?
If I have many sheets in my workbook, how can I get the index number of the active sheet?? Is there a property or method for "ActiveSheet" which gives back this value?? msgbox activesheet.index Robert Crandal wrote: > > If I have many sheets in my workbook, how can I get > the index number of the active sheet?? Is there > a property or method for "ActiveSheet" which gives > back this value?? -- Dave Peterson ...

CString manipulation
Hi, I would like to replace \r\n, \n\r or \n by a <BR> in a CString. I have some methode to do it but they are not optimized. I am starting with that : for (int i = 0; i < strHtml.GetLength(); i++) { if (strHtml.GetAt(i) == '\r') { if (strHtml.GetAt(i + 1) == '\n') { strHtml = strHtml.Mid(i, 2); strHtml.Insert(i, _T("<BR>")); } } else if (strHtml.GetAt(i) == '\n') { if (strHtml.GetAt(i + 1) == '\r') { strHtml = strHtml.Mid(i, 2); strHtml.Insert(i, _T("<BR>")); } else ...

How do I substitute text with a picture as a "comment" to a cell
I have a spreadsheet where in one of the columns I want to have the cells display a picture as I mouse over, akin to creating a comment for the cell. Is that feasible? Thank you, Arik http://www.contextures.com/xlcomments02.html#Picture Tells exactly how to do it. "Arik S" wrote: > I have a spreadsheet where in one of the columns I want to have the cells > display a picture as I mouse over, akin to creating a comment for the cell. > Is that feasible? > > Thank you, > Arik ...

Text box margins
Hello Folks When I create a (nearly) full page text box and format this to four columns to fill with relatively small text, each column of text appears too close to one another. If I re-format this text box and alter the right and left margins it only changes the left and right columns, not the two middle ones.] I do have a workaround of creating and linking four separate text boxs, but wonder if there was a way to alter the margins in the middle two columns. Hope you can understand my logic. Your help is and always has been very much appreciated. Thanking you in anticipation. <->&...

Opening a text file
I have 390 MB data text file, i try to open this file with MS Excel only a part of the file was open. Please help me open this file with MS Excel or MS Access Thanks Khawaja anwar That is huge. Do you need all the data in one single place and/or at the same time? What are you going to do with the data ? What does it contain ? Best wishes Harald "Khawajaanwar" <Khawajaanwar@discussions.microsoft.com> skrev i melding news:09EC3080-3D08-4E34-B59F-0D5218053356@microsoft.com... >I have 390 MB data text file, i try to open this file with MS Excel only a > part of the f...

reverse/manipulate order of numbers?
I need to have to control of numbers in a list of numbers and puncuation, keeping the solution as numbers & not a new, text string. List: 01(25) 02(25) 100(25) 01(26) 03(26) 10(26) I want to take the list and make it: (25)01 (25)02 (25)100 (26)01 (26)03 (26)10 Can you help? Thanks, Bob You can perform such conversion with a worksheet formula. For example, if a cell A1 contains the text "100(24)". Then, in cell B1, you type: =RIGHT(A1,LEN(A1)-FIND("(",A1)+1) & LEFT(A1,FIND("(",A1)-1) The formual will return the answer "(24)100" Once yo...