show columns on other worksheet without using formulas

Hi,

I have 1 worksheet containing lot's records.
Just like in access, each line is 1 record, and each column contains data.

ex:

WORKSHEET 1:

        name    title    address     city
1      dfjw    kjhk    kjhkj        kjhk
2      lmkj    lkjlk    lkjkuf        guyg
3      drdtg   xcx    yjutuy        hgyy


Now I want to have a selection of columns on worksheet 2:
ex, only name and title:

WORKSHEET 2:

        name    title
1      dfjw    kjhk
2      lmkj    lkjlk
3      drdtg   xcx


I will only add/modify rows in worksheet 1.
And I want that this is filled automaticaly in worksheet 2.
I do not want formulas in worksheet 2:  like for example: cell A1 =
worksheet1!A1
And I do not want to run a macro each time I add or modify a row in
worksheet 1

How could I do this??
All ideas are welcome !

THX everyone,
Nic.


0
10/18/2005 9:42:11 AM
excel 39879 articles. 2 followers. Follow

3 Replies
321 Views

Similar Articles

[PageSpeed] 42

Hi Nic,

Try:
'==================>>
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range

    Set rng = Range("A1:B100")

    Application.EnableEvents = False
    If Not Intersect(Target, rng) Is Nothing Then
        Sheets("Sheet2").Range(Target.Address).Value _
                                    = Target.Value
    End If
    Application.EnableEvents = True

End Sub
'<<==================

This is worksheet event code and should be pasted into the worksheets's code 
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************

With this code in place, any changes to the range A1:B100 on Sheet1 will 
automaticall be recorded in the corresponding cells on Sheet2.

Change the range and sheet names to suit your requirements.

---
Regards,
Norman


"nicholas" <murmurait1@hotmail.com> wrote in message 
news:%2355sJi80FHA.3956@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have 1 worksheet containing lot's records.
> Just like in access, each line is 1 record, and each column contains data.
>
> ex:
>
> WORKSHEET 1:
>
>        name    title    address     city
> 1      dfjw    kjhk    kjhkj        kjhk
> 2      lmkj    lkjlk    lkjkuf        guyg
> 3      drdtg   xcx    yjutuy        hgyy
>
>
> Now I want to have a selection of columns on worksheet 2:
> ex, only name and title:
>
> WORKSHEET 2:
>
>        name    title
> 1      dfjw    kjhk
> 2      lmkj    lkjlk
> 3      drdtg   xcx
>
>
> I will only add/modify rows in worksheet 1.
> And I want that this is filled automaticaly in worksheet 2.
> I do not want formulas in worksheet 2:  like for example: cell A1 =
> worksheet1!A1
> And I do not want to run a macro each time I add or modify a row in
> worksheet 1
>
> How could I do this??
> All ideas are welcome !
>
> THX everyone,
> Nic.
>
> 


0
normanjones (1047)
10/18/2005 10:10:22 AM
Hi

There is a camera within Excel. If you go to View/Toolbars/Tools  down near 
the bottom of the list is a camera option. Drag this to your toolbar.
Select the cells you wish to see and click the camera. You can now go to 
your main sheet and drag your cursor to create a window within Excel that 
will show what you have just captured - and it updates too!
There must be a keyboard shortcut for the camera, but I don't know what it 
is.
It might be of interest.

Andy.

"nicholas" <murmurait1@hotmail.com> wrote in message 
news:%2355sJi80FHA.3956@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have 1 worksheet containing lot's records.
> Just like in access, each line is 1 record, and each column contains data.
>
> ex:
>
> WORKSHEET 1:
>
>        name    title    address     city
> 1      dfjw    kjhk    kjhkj        kjhk
> 2      lmkj    lkjlk    lkjkuf        guyg
> 3      drdtg   xcx    yjutuy        hgyy
>
>
> Now I want to have a selection of columns on worksheet 2:
> ex, only name and title:
>
> WORKSHEET 2:
>
>        name    title
> 1      dfjw    kjhk
> 2      lmkj    lkjlk
> 3      drdtg   xcx
>
>
> I will only add/modify rows in worksheet 1.
> And I want that this is filled automaticaly in worksheet 2.
> I do not want formulas in worksheet 2:  like for example: cell A1 =
> worksheet1!A1
> And I do not want to run a macro each time I add or modify a row in
> worksheet 1
>
> How could I do this??
> All ideas are welcome !
>
> THX everyone,
> Nic.
>
> 


0
Andy
10/18/2005 11:08:48 AM
excellent !!!

THX
Nic

<Andy> wrote in message news:ORffzQ90FHA.164@TK2MSFTNGP10.phx.gbl...
> Hi
>
> There is a camera within Excel. If you go to View/Toolbars/Tools  down
near
> the bottom of the list is a camera option. Drag this to your toolbar.
> Select the cells you wish to see and click the camera. You can now go to
> your main sheet and drag your cursor to create a window within Excel that
> will show what you have just captured - and it updates too!
> There must be a keyboard shortcut for the camera, but I don't know what it
> is.
> It might be of interest.
>
> Andy.
>
> "nicholas" <murmurait1@hotmail.com> wrote in message
> news:%2355sJi80FHA.3956@TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have 1 worksheet containing lot's records.
> > Just like in access, each line is 1 record, and each column contains
data.
> >
> > ex:
> >
> > WORKSHEET 1:
> >
> >        name    title    address     city
> > 1      dfjw    kjhk    kjhkj        kjhk
> > 2      lmkj    lkjlk    lkjkuf        guyg
> > 3      drdtg   xcx    yjutuy        hgyy
> >
> >
> > Now I want to have a selection of columns on worksheet 2:
> > ex, only name and title:
> >
> > WORKSHEET 2:
> >
> >        name    title
> > 1      dfjw    kjhk
> > 2      lmkj    lkjlk
> > 3      drdtg   xcx
> >
> >
> > I will only add/modify rows in worksheet 1.
> > And I want that this is filled automaticaly in worksheet 2.
> > I do not want formulas in worksheet 2:  like for example: cell A1 =
> > worksheet1!A1
> > And I do not want to run a macro each time I add or modify a row in
> > worksheet 1
> >
> > How could I do this??
> > All ideas are welcome !
> >
> > THX everyone,
> > Nic.
> >
> >
>
>


0
10/18/2005 11:57:21 AM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Letter Writing assitant to be used with Prospects
Would like to see Letter Writing Assitant to be extended to be used with Prospects. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=73b8a531-e5c3-4d8b-91ae-5b1758b9f1a5&...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

I cannot get the pictures to show on screen in Publisher web previ
I have tried embedding and linked but nothing seems to work. Do I have to apply a master page? It is to be a one page web site. This is probably so basic but I am trying to teach myself how to use it. What do I have to do to have them show in preview and once uploaded? Thank you. Are you using FireFox to preview or IE? DavidF "rendul" <rendul@discussions.microsoft.com> wrote in message news:C9B0A640-6C87-4E9A-8A0E-5F32F7DAC0DE@microsoft.com... >I have tried embedding and linked but nothing seems to work. Do I have to > apply a master page? It is to be a one ...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Receiving POP3 mail into Outlook without Send/Receive
I want the POP3 mail to be received into Outlook (If open) when the mail arrives in POP3 account. Is that possible? Set an Automatic Polling Interval. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, delanda asked: | I want the POP3 mail to be received into Outlook (If open) when the | mail arrives in POP3 account. Is that possible? ...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Showing 3:54PM instead of 3:54:03PM
How do you get rid of the seconds in the time area. I have changed the formatting in the time. I use the excel file as a data source. I include the time in the mail merge. It always shows up with the seconds in the time. Very frustrating. HELP PLEASE!! TJ it may be formatted as text, so won't respond to changing the time format. if it is text, the TIMEVALUE formula will convert it to a decimal-based time value which can then be formatted by using Excel's normal Number formatting-- to get rid of the seconds. Eddie O "TJ" wrote: > How do you get rid of the secon...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

Report to show Item Class Distribution Amounts
We would like to create a report, using Crystal Reports, that would show the following: dollar amount break down of the Sales Distribution accounts (COGS and Sales) per item class based on a date range. What is the most accurate way of going about this? We could only think of this method: (in short) sum the Ext Price based on SOP30300.CSLSINDX and SLSINDX and hope it matches the SOP10102 summed distribution amounts. Any advice would be appreciated. Thanks in advance. With the SLSINDX you would use the Extended Price and the CSLSINDX you would use Extended Cost. You would probably ...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Doesn't show busy,when scheduling meeting
Hello I have a user, who has meetings scheduled and shows up in her Calendar fine. But, when she or someone else tries to schedule new meeting, invite her, it doesn't show her busy schedule. It indicates as if she is available all time. Secondly, when she edits the meeting and marks as "Out of Office", than it shows up as busy. Her Out of Office is Off. Outlook 2000 with Exchange corporate setup. Any help will be appreciated. Thanks ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...