Linking information form one worksheet to another

Hi, i have a big problem

i want to create a link between some columns in two seperate worksheets, so 
that when i type a name on the master sheet it would give me the required 
information.Dont know if i explained this right, lets say on the master sheet 
i have columns : Name,Sex and Height on columns A,D and BH respectively i 
want that if i type a person's name in worksheet 2 it should give me the 
results on columns A,C,F in that worksheet. Please help i need it asap for my 
director 
0
lawmere (5)
11/9/2005 12:39:03 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
732 Views

Similar Articles

[PageSpeed] 27

One way ..

Assuming your "master" sheet is named: Master, with data in row1 down

In Sheet2,
Names would be entered in A1 down
Put in C1: =INDEX(Master!D:D,MATCH(A1,Master!A:A,0))
Put in F1: =INDEX(Master!BH:BH,MATCH(A1,Master!A:A,0))
Copy C1 and F1 down as far as required

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"lawmere" <lawmere@discussions.microsoft.com> wrote in message
news:4144C2E7-899F-4FA4-B1A1-C84417CDC696@microsoft.com...
> Hi, i have a big problem
>
> i want to create a link between some columns in two seperate worksheets,
so
> that when i type a name on the master sheet it would give me the required
> information.Dont know if i explained this right, lets say on the master
sheet
> i have columns : Name,Sex and Height on columns A,D and BH respectively i
> want that if i type a person's name in worksheet 2 it should give me the
> results on columns A,C,F in that worksheet. Please help i need it asap for
my
> director


0
demechanik (4694)
11/9/2005 2:13:51 PM
hi Max 

It wont work for me when i tried to upload it it would not let me put 
anything in F1 or maybe i didnt explain myself prperly am so sorry am just 
panicking. will start again if you could help explain in a novice form pls:

Worksheet 1 is called Master with all the stored information and is designed 
as follows

Name   Sex   Age  Height  Occupation  Education  Religion


Worksheet 2 is designed as an inspection sheet as follows:

Person   Location  Sex  Areapostcode   Attitude  Height


now what i want to do is if i type in a person's name in worksheet 2  i want 
it to automatically fill in similar columns with worksheet1 i.e [Sex and 
Height]

Regards

Lawrence

"Max" wrote:

> One way ..
> 
> Assuming your "master" sheet is named: Master, with data in row1 down
> 
> In Sheet2,
> Names would be entered in A1 down
> Put in C1: =INDEX(Master!D:D,MATCH(A1,Master!A:A,0))
> Put in F1: =INDEX(Master!BH:BH,MATCH(A1,Master!A:A,0))
> Copy C1 and F1 down as far as required
> 
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "lawmere" <lawmere@discussions.microsoft.com> wrote in message
> news:4144C2E7-899F-4FA4-B1A1-C84417CDC696@microsoft.com...
> > Hi, i have a big problem
> >
> > i want to create a link between some columns in two seperate worksheets,
> so
> > that when i type a name on the master sheet it would give me the required
> > information.Dont know if i explained this right, lets say on the master
> sheet
> > i have columns : Name,Sex and Height on columns A,D and BH respectively i
> > want that if i type a person's name in worksheet 2 it should give me the
> > results on columns A,C,F in that worksheet. Please help i need it asap for
> my
> > director
> 
> 
> 
0
lawmere (5)
11/9/2005 2:54:07 PM
Could you upload a copy of your file, and post a *link* to it here in reply?
Think there's a need to see your actual set-up
One free filehost you could use: http://www.flypicture.com/

Note: Pl do not post any attachments to the newsgroup.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"lawmere" <lawmere@discussions.microsoft.com> wrote in message
news:0A68F52B-ED7D-45DE-9713-A746CBE146FB@microsoft.com...
> hi Max
>
> It wont work for me when i tried to upload it it would not let me put
> anything in F1 or maybe i didnt explain myself prperly am so sorry am just
> panicking. will start again if you could help explain in a novice form
pls:
>
> Worksheet 1 is called Master with all the stored information and is
designed
> as follows
>
> Name   Sex   Age  Height  Occupation  Education  Religion
>
>
> Worksheet 2 is designed as an inspection sheet as follows:
>
> Person   Location  Sex  Areapostcode   Attitude  Height
>
>
> now what i want to do is if i type in a person's name in worksheet 2  i
want
> it to automatically fill in similar columns with worksheet1 i.e [Sex and
> Height]
>
> Regards
>
> Lawrence


0
demechanik (4694)
11/9/2005 3:15:41 PM
kindly find attached the columns i want to link are 
*http://www.flypicture.com?display=updone&id=rd33mq7Z * for the master 
worksheet and *http://www.flypicture.com?display=updone&id=rd33mq7a* for the 
second worksheet. 

The columns i want to link with are in supermarket3

"Max" wrote:

> Could you upload a copy of your file, and post a *link* to it here in reply?
> Think there's a need to see your actual set-up
> One free filehost you could use: http://www.flypicture.com/
> 
> Note: Pl do not post any attachments to the newsgroup.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "lawmere" <lawmere@discussions.microsoft.com> wrote in message
> news:0A68F52B-ED7D-45DE-9713-A746CBE146FB@microsoft.com...
> > hi Max
> >
> > It wont work for me when i tried to upload it it would not let me put
> > anything in F1 or maybe i didnt explain myself prperly am so sorry am just
> > panicking. will start again if you could help explain in a novice form
> pls:
> >
> > Worksheet 1 is called Master with all the stored information and is
> designed
> > as follows
> >
> > Name   Sex   Age  Height  Occupation  Education  Religion
> >
> >
> > Worksheet 2 is designed as an inspection sheet as follows:
> >
> > Person   Location  Sex  Areapostcode   Attitude  Height
> >
> >
> > now what i want to do is if i type in a person's name in worksheet 2  i
> want
> > it to automatically fill in similar columns with worksheet1 i.e [Sex and
> > Height]
> >
> > Regards
> >
> > Lawrence
> 
> 
> 
0
lawmere (5)
11/9/2005 3:50:07 PM
See the sample implemented at:
http://cjoint.com/?ljrEMPjI4L
Linking_Info_Between_Sheets_lawmere_misc.xls

Part number entered in A7 down

Put in C7:
=INDEX(Master!D:D,MATCH(TEXT(A7,"0000"),Master!A:A,0))

Put in F7:
=INDEX(Master!BH:BH,MATCH(TEXT(A7,"0000"),Master!A:A,0))

Copy C7 & F7 down the columns
as far as there is data entered in col A

" TEXT(A7,"0000") " is used instead of just: " A7 "
to convert the input lookup value in A7 to text
as the part number in Master!A:A is text number, not real number
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


0
demechanik (4694)
11/9/2005 4:34:50 PM
Hi Max,

Thanks fr the solution sorry i couldn't get back to yesterday had to dash 
off . It works fine the only problem is that i cant seem to vary the part nos 
in the column to maybe different characters or digits it seems to be fixed at 
just 4 characters how may i change this pls.

regards



"Max" wrote:

> See the sample implemented at:
> http://cjoint.com/?ljrEMPjI4L
> Linking_Info_Between_Sheets_lawmere_misc.xls
> 
> Part number entered in A7 down
> 
> Put in C7:
> =INDEX(Master!D:D,MATCH(TEXT(A7,"0000"),Master!A:A,0))
> 
> Put in F7:
> =INDEX(Master!BH:BH,MATCH(TEXT(A7,"0000"),Master!A:A,0))
> 
> Copy C7 & F7 down the columns
> as far as there is data entered in col A
> 
> " TEXT(A7,"0000") " is used instead of just: " A7 "
> to convert the input lookup value in A7 to text
> as the part number in Master!A:A is text number, not real number
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> 
> 
> 
0
lawmere (5)
11/10/2005 9:22:03 AM
Perhaps its better to use instead:

In Sheet2,

In C7: =INDEX(Master!D:D,MATCH(A7&"",Master!A:A,0))
In F7: =INDEX(Master!BH:BH,MATCH(A7&"",Master!A:A,0))

Then copy down as before
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"lawmere" <lawmere@discussions.microsoft.com> wrote in message
news:2AB50458-C229-44B0-B79E-47F20EF0FD74@microsoft.com...
> Hi Max,
>
> Thanks fr the solution sorry i couldn't get back to yesterday had to dash
> off . It works fine the only problem is that i cant seem to vary the part
nos
> in the column to maybe different characters or digits it seems to be fixed
at
> just 4 characters how may i change this pls.


0
demechanik (4694)
11/10/2005 10:24:53 AM
Reply:

Similar Artilces:

Does every one else have to micro manage MS$?
Just wondering if I am alone on this but it seems like with this app the longer you use it the more things come up that are just baffling.. For me it is duplicate transactions showing up, things not matching from the bills area back to the account register and the latest is ever transaction from my bank comes by default with NICOR as the payee and marks it as a utility. I know allot of the problem is the different banks and companies the software has to work with but common these discrepancies and constant need of babysitting almost makes it a wash for any efficiencies gained. I wen...

Deleting information
Hi All HYCH Am looking for some assistance with the following, i use a form to enter details about individuals, this enters the info into a column at the end of the line and then sorts into alphabetical order. no problem with this, But, I would like to use a form to select an individual from anywhere in the range and delte their details, every name is range from D3:EU3 (Combo box initialises this ok) And the data is listed under each person down a column, is there someway to adjust the code below to allow this to work?? This section works fine!!! Private Sub UserForm_Initialize() Dim ...

Delete a logo from Personal Information
I an studying up on Publisher so I can teach it and I have come across a problem I don't see answered anywhere. When you first open Publisher and go to personal information the logo field is blank----obviously. But when you add a logo I see no where it can be deleted, only changed or edited. I need to be able to start fresh with a new class. Anyone know how to delete this out. Thanks ahead of time. David Frazier Add, change, or remove personal information data http://office.microsoft.com/en-us/assistance/HP030695661033.aspx Edit a logo for a personal information set http://offi...

filter form on value from linked table
Is it possible to filter a form (based on table A) on a value in a table B where the tables are linked? Table A ID lastname firstname companyid ... Table B companyid companyname ... I would like to filter the form records (Table A) by using as input the company name (or part of it) from a textbox. Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200704/1 Sorry for this but I forgot I would like to use like frm.Filter = as I already use it to filter the form based on values from another txtbox on the lastname -- Message poste...

Protecting worksheets
Hi there! I have an excel workbook with 8 worksheets (1 for each of my bosses). Rather than emailing each guy a copy of the file every month for completion then having to merge the data back into 1 workbook, I want to send them a link to a file, saved in a shared area and let them each complete their bit. The problem is the sensitivity of the information - I only want each boss to have access to their worksheet. I'm thinking along the lines of password protecting each sheet seperately with a different password - is this feasible or practical? Any tips / hints / advice gratefully ...

Adding VLOOKUPS across multiple worksheets
Hi, I am trying to add several VLOOKUP results obtained from data in several worksheets. My formula is : - =VLOOKUP(A5,'Common Costs - Budget'!A5:X74,2,FALSE)+VLOOKUP(A5,'Brighton - Budget'!A5:X53,2,FALSE)+VLOOKUP(A5,'Hove - Budget'!A5:X56,2,FALSE) I have several worksheets all with similar budget information on and, for example, I need to all the payroll costs from each of the sheets. Not all the sheets will always have costs on so I was thinking of doing lookups and adding them together but I just keep getting the #N/A result. Is there a way to...

link Access workbook to Excel workbook
Can I link information from an Excel worksheet to an Access worksheet? Hello, In Access, clic on File menu, External data, link tables and follow each step. You'll have to define first row as heading label and that's it. regards "Toinett" wrote: > Can I link information from an Excel worksheet to an Access worksheet? ...

Reset field based on the the results in another field
I have a picklist field on a form and a field that is using the radio button. When the user changes the selection on the picklist field to "completed", javascript checks the value of the radio button field. If the field is set to no,an alert pops up to the user. The picklist field should then reset back to the previous value. I have only been able to get the code to set the field to 'blank' (not acceptable). Please help. -- Onetreeup Hi, doesn't something like this do the trick: in the onload you capture the value of the picklist when the form is loaded. and yo...

Forms: can a cell itself be clickable?
Hi! I'm trying to create a form for my boss. I'm using the forms toolbar, but what it does is throw in a check box for instance, but if I change the cell in any way, the "picture" of the click box stays where it's at. Any way to change this? In other words, I don't want to the click box to be a picture, I want it to be an integral part of the cell, so I can modify it as needed (ie. place it centrered horizontally or vertically etc. Am I dreaming in colour? Thanks! Carmen I've seen a macro that will insert a checkmark when you select the cell, but I&#...

Query: Link data between worksheets?
I'm trying to find a shortcut to save me some time. This is the problem: I'm setting up a master worksheet, and 30 to 40 "slave" worksheets tha need to run off it (all within the same workbook). The slave worksheet all retrieve data from the master. The data is setup to run across i rows (i.e. Row 1 contains 1 record). Each record has an individua identification number entered in column A. Is there a way that I can tell the slave sheets to copy an entire row based purely on the identification number entered in column A? Thanks in advance if anyone can help, as this could ...

Combo Box on Forms
Hi, I am using a combo box on a form that has several items w/ one being Other. If the user selects other I want them to be able to input what they want so that it will go into the table instead of just the "Other". I just am not really sure on how to do that. Can you help? Thanks, Jaime In design view click on VIEW - Properties and the the combo. Then change Limit To List to No. -- KARL DEWEY Build a little - Test a little "jseger22@yahoo.com" wrote: > Hi, > > I am using a combo box on a form that has several items w/ one being > Other. If the use...

Digital signature information
I did an upgrade from Office 97 to Office XP and replaced all the existing files with the new. When I start up Word I get the following Microsoft Word message about Macros that point to Digital Signature Information with the following Signer Information: Adobe System Inc. is the Name of signer and E-mail is Not Available that shows Signing time: of Thursday, September 13, 2001 12:08:21 PM. The Counter signature is VeriSign Time Signature. How can I disable this and when I look on the VeriSign site I get an article about it being a VeriSign glitch. Can you be of some help. ...

Pagination on a Continuous Form
I would like to have one continuous form hold a list of 30 records per page, and be able to see the page numbers (and use them) at the top and/or bottom of the form, such that a total of a couple hundred records (or more) can be displayed in groups of 30 at the request of clicking the page number (similar to that of a webpage, but only an MS Access Form). On Fri, 5 Mar 2010 11:23:01 -0800, VWP1 <VWP1@discussions.microsoft.com> wrote: You'll have to do all the work yourself, because such interface is not natively supported. Would be an interesting project though. -To...

Links on my website don't work
With IE 8 my links *to go from one page to a different page* on my website do not show up however the website works just fine in chrome, firefox and earlier versions of IE. Any clues how to fix this? IF you need the website it is www.kfoxweatherfox.com. On the left hand side should be a whole list of pages to go to such as knowledge, photos etc. Date: 4/3/2010 10:20 AM, Author: kfoxweatherfox Wrote: > With IE 8 my links *to go from one page to a different page* on my website do > not show up however the website works just fine in chrome, firefox and > earlier ve...

Need access advise regarind linked versus imported tables
I have designed an application that uses a linked csv file as the main table. The reason for this is because another user creates this file and jsut puts it into a designated folder on their computer. Here is the issue. The csv file contains approximately 20K records. I cannot create any type of index on a linked file. Am I better off importing the file into access (or reading the linked table and inserting them into another table with an index)? Is there an easy way to perform something like this? Will this increase performance? I am pretty new to access and am looking for any and all...

Emailing information from a query
I would like to email the information from a query in the body of my email message, Ex. Query Name.emaildates with the following 3 fields id# date due assigned to 0001 05/01/2007 M. Smith 0002 05/02/2007 J. Smith etc Can someone please help. I looked at http://www.granite.ab.ca/access/email/formatted.htm. but that totally confused me. ...

get data form cells sequentially
Hi there, I have a spreadsheet i am creating which is giving me some amount o hassle!!! I'm sure it is possible to do this but i just cant figure ou how:- I have one worksheet which has many copies of the same table, eac table is consistantly ontop of the other i.e. first table uses cells A - G10 (a rectangle of cells). The dates i am trying to use are in a pattern of a nine row seperatio i.e. first date is in cell G2, next one is G11, next one G20, and s on. I need to find out how i can make a formula which i could copy whic would grab the date out of these cells in sequence for pl...

How do i print an excel file in the form of a booklet?
I have a list of phone numbers and addresse that I need to print in the form of a small booklet that I can carry in my purse. How do I print it? What program and how to set it up? Also I want ohotos on the left and data on the right side pages. I'm not sure excel is the best application for this kind of thing. I'm sure you could do it (after a few hours/days/months of frustration!), but maybe you could find a better application by searching google. Or even put the data into MSWord and use that for all the nice formatting. shireen wrote: > > I have a list of p...

Linking Word And Excel
I am trying to link several fields in Excel to a Word Doc. I'm linking the fields using Ctrl-Paste as Hyperlink. The linking works fine. However, when I try to save the files in different folders or move the files, all the links are lost. This makes it very hard to replicate the linking for my 6 analysts. Any help would be greatly appreciated. Brady -- **************************************************************************** Brady T. Finney Atlantic Management Company, Inc. Orchard Park - Suite A12 875 Greenland Road Portsmouth, NH 03801 603-436-8009 (Office) 617-413-4554 (Mobil...

Form with adding
Hello I want to create a form for people who use a cabin The columns titles are: name, number of nights, age group, $ per night, then total $'s The $'s per night depends on the age group. It's $10 for ages 0-12, $20 for 13-18, and $30 for 19+. I'm doing a drop down menu for the age groups. Based on their choice, I want the $'s per night to come up automatically in the next column My questions are 1) Is it best to be doing this in Excel, or should I use Word 2) Assuming Excel, how do I set up the $'s per night based on the drop down menu selection 3) I'd like ...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Restoring another PST?
Hi everyone, I use the MS Outlook Backup tool, and it backs up daily. Early this week I imported the backed-up PST file ( over 1GB ) into my new PC and after a bit of fiddling to show the folders it did open up all the ones I wanted, yet seemed to be an old backup - maybe 12 weeks out of date. I had to use the machine so kept it like this, and have of course received many mails since then. I see another copy of the backup[ PST file and the date says it was created on Feb 19th, which would be right. My ( evantual! ) question is: how do I, in idiot-proof steps, go about restoring this other...

CRM, Reporting Services & Forms Authentication
Our company has an app with forms auth that is integrated with Reporting Services. We have forms auth working successfully with Reporting Services. We'd like to integrate CRM and still use forms auth. Is this possible with CRM? Would anyone be kind enough to point me to any documentation or examples of CRM w/ forms auth & Reporting Services? ...

click on page form tab to filter subform
I have page tab with following names: Flat Rate, Global Rate, MSRP Rate. I would like to have subform only show the rates for each page tab. How do I go about doing this. I am new to VBA and from reading all the threads, I can't seem to find the one that fits my exact need. Please explain step-by-step since I am new. Let me know if I need to provide more details. Thank you. Hi Kris Presumably you have a field in the RecordSource of your subform that contains one of the three values (Flat Rate, Global Rate, MSRP Rate) or some other text or code that corresponds to these values....

Stripping Blank Characters form Imported Data
I am trying to verify 2 worksheets full of data. One has been imported from Visual Basic and the other from Business Object. The Business object sheet, brings over the entire field not just the charaters needed. Example: For the city field there are 50 characters available. If you use Nashville, the Business Object brings over 50 characters where the Visual Basic brings over 9. When trying to find descrepancies, this brings back a false positive if the city is the same. Is there a way to manipulate that data when it is sent to Excel? The easiest way to deal with this would be to find...