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
812 Views

Similar Articles

[PageSpeed] 42

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:

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

Public information store
Exchange 2K-Under the Properties for a new public folder I have created, the Directory Rights tab is grayed out. Any input would be appreciated -- Joe M On Tue, 12 Jul 2005 12:01:03 -0700, "Joe M" <JoeM@discussions.microsoft.com> wrote: >Exchange 2K-Under the Properties for a new public folder I have created, the >Directory Rights tab is grayed out. > >Any input would be appreciated Its grayed out until you mail-enable it. ...

not able to link chart points from same series
I'm working on a chart that pull information from several pages. Tha table is able to plot information correctly in the graph but I can't seem to link the dots on the chart with a line. Everytime I try a linear chart or a scatered chart with smoth lines, the lines start from the x axis and reach the dot rather than connecting dots from the same series. suggestions would be greatly appreciated. If the points are not in the same series (i.e., the data source is from different sheets, not a single summary sheet), you will never link them. If you have gaps, true actual blanks (n...

The Links from e-mails don't work
If I copy the links and insert it into IE 8 it reads it. Trying to link over from an e-mail directly is not working. If I copy the link and go to IE 8.0 the link (URL address) works. It was working before. pgman27 You mean if you click on the link in a message it does nothing, not even an error message? Are you certain IE has all its defaults? Open the Default Programs applet, which you can access either from the Start menu or via the Control Panel, then click the first item: "Set your default programs." After a few seconds, a list of programs comes up. Click on...

how to set the worksheet direction in Excel XP
in a right-to-left interface the vertical side showing the row numbers of the excel worksheet appears on the right side of the sheet. how do I change it' to appear on the left side? Hi does tools / options / international - default direction ... help? Cheers JulieD "inbal" <inbal@discussions.microsoft.com> wrote in message news:CC3B0D65-78B2-4B5B-A6B2-789773203853@microsoft.com... > in a right-to-left interface the vertical side showing the row numbers of > the > excel worksheet appears on the right side of the sheet. how do I change > it' > to a...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

Yet another duplicate record dilemma
I have a table with records where one field are duplicates. I'm able to query to find duplicates and delete them, however what I need to do is find the duplicates, produce a total from another field, delete the duplicates and update the record field with the new total. Use the Find duplicates wizard, the build an Update query and either add to the field: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField + Query1.MyField or just update it: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = Query1.MyField Then delete the duplicate data. -- Ar...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

linking worksheets #3
I know ho to link cells to oher cells. But is there a convenient way to link ranges or worksheets to others. Hi just link the first cell e.g. ='sheet1'!A1 and copy this formula for your range. The reference will adapt automatically -- Regards Frank Kabel Frankfurt, Germany j van c wrote: > I know ho to link cells to oher cells. But is there a convenient way > to link ranges or worksheets to others. ...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

Filling Web Form
Dear freinds Hello, I have written code in vc++ using MFC to fill the Webform using HTTP post method and I'm able to fill the form but I'm facing a problem that I'm unable to fill login name and password. So any one amongst you have any Idea or help then please help me With Regards Sachin ...

How to make a Add key disabled after adding one record unless
Hi, I have a form where I am adding records. There are two boxes where one has to put the lineitemnumber and another box where one has to put the description. Unless the above two boxes are filled in the Add command box need to be disabled. Now, the problem is after adding one record the Add button is enabled and I cannot disable it. I would appreciate any help to solve this. The error message is: The methos is not supported The following is the code: Private Sub cmdadd_Click() On Error GoTo Err_cmdadd_Click If (IsNull(Me.LineItemID) = True) Or (IsNull(Me.Description) = True) Then ...

Excel worksheet with VBE codes don't work elsewhere
Hi, Some of my excel worksheets with embedded controls and VBA codes don' work when I open it on another PC. Is there another way to make i work? Thx -- lazybea ----------------------------------------------------------------------- lazybear's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3519 View this thread: http://www.excelforum.com/showthread.php?threadid=54955 Specifically what problems are you having? Saying "don't work" means absolutely nothing. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC ww...

Add new record through Form view
I have a table which is linked to a form, i would like to have a command button which will add a new record to my table. On the click of command button it should view the last empty row of my table in a form. On Sat, 26 Dec 2009 11:11:01 -0800, Ranjith Kurian <RanjithKurian@discussions.microsoft.com> wrote: >I have a table which is linked to a form, i would like to have a command >button which will add a new record to my table. >On the click of command button it should view the last empty row of my table >in a form. The Click event should show [Event Proced...

Accessing my information offline
I have updated my Outlook to 2002 on my notebook computer and when it is connected on the Lan at my office(new ethernet connection) I can see my calendar, addressbook, e- mails etc. However, when I disconnect and bring my notebook home, and try to access calendar, etc, it says I must sychronize while I am still connected to the Lan. My previous version of outlook didn't have this problem. I synched it on the Lan and it worked. This one won't. Any ideas???? How do you have your folders marked for Offline use under tools->Send/Receive settings? --� Milly Staples [MVP - Outlo...

Information Store Corrupted
Guys,, I think I'm in very deep trouble. I have two Exchange 5.5, one for outgoing mail and one for incoming, the incoming mail sever is a W2K and is the Domain Controller. My DNS server is a separated dedicated server. I had a big power failure last thursday night. The three server sudently shut off and restart again after the AC came back. On friday morning I realized we didn't have email service, I checked the outgoing server and it was OK, but on the incoming server the Information Store Service was not starting, I went to the web and found this article about how to reco...

Link and Embed in 2003
Using the Insert>Picture>From File comand we can use either the 'insert' or 'link to file' command to insert a picture. During our development we prefer to link pictures then when completed save the .ppt with all pictures embedded. 1. When pictures are in slides during design is it possible to tell if the picture is linked or embedded? 2. Is there a way to embed any/all linked pictures when the slide presentation is completed without going to each slide and reinserting the picture as 'insert' Cheers! -- Blumburg In article <07E6153F-5D...

Messenger emoticons
I have changed laptops and I did grab the old laptops custom emoticons folder (all in dt2 and id2 file endings.) But when i copy everything in the folder and add it to my new laptops custom emoticons folder... they get added (i.e. show up in the folder) but the images/gifs or names dont show up on the actual msn... *what gives*? Do I have to change the dt2 endings to gif or jpeg and go to "create" in msn for each of them to add them in? (I tried with one and it worked) Only problem is i have alot, like 203 dt2 files so changing the ending to .gif and adding each singu...

How do I print 4 postcards to one page to match my postcard paper
I bought perforated postcard paper that has 4 postcards per sheet. How do I create that in Publisher? I created it exactly how I want it to look and then realized it's only printing 2 per page. Thanks, Lauren Which version of Publisher are you using? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Lauren" <Lauren@discussions.microsoft.com> wrote in message news:DDA6778A-5DF0-4278-A1CA-896DC49FAFBA@microsoft.com... >I bought perforated postcard paper that has 4 postcards per sheet. How do >...

trying to link maps and pie charts
Hello I was trying to link sales data stored in an excel table with a specific country in MapPoint. This software allows you to do this very easily. You can even picture the data in piecharts (% sales for each competitor). However there is very little flexibility with the look of the result: you cannot choose the color of the slices of the pie, you may not display data on the graph, and the maps do not look very professional. I know it is possible to link automatically a table with a shape in visio. Can this shape be a given country/region on a map? Can the callouts have th...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

make outlook automatically delete html mail with linked images
The junk mail filter should be able to automatically delete html mail with linked images ---------------- 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/office/community/en-us/default.mspx?mid=9e1b783b-c9c1-44f2-a1dd-93da59216c56&dg=microsoft.public.outloo...

MS Access 2007, Forms
When using the "Forms Wizard", I need to use the "Style: Ricepaper", but it isn't available in the 2007 version and I have not been successful trying to find it in Office Online or on the web. Does anyone know where I can get it? Access 2007 has 25 AutoFormats, but as you noticed, they are virtually all new ones. It's actually nice to see that these were updated. To get the older Ricepaper style you could probably create a form in an earlier version of Access and then import it into your current database. If you're using the ACCDB file format, you would...

Where can I get a good holiday booking form for my golf business?
I need a quality Booking Form for my Golf Holiday business on the Costa Del Sol. One which includes sections for accommodation, dates, numbers, flight enquiries and car hire enquiries. Any help would be much appreciated on a tight budget. Thanks Dave Wrong forum. Try templates or doing it yourself. :-) -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Dave" wrote: > I need a quality Booking Form for my Golf Holiday business on the Costa Del > Sol. > One which includes sections for accommodation, dates, numbe...