How to lookup and display info?

Am sure this is really easy but the logic of it is escaping me. I'm trying to 
the following to make an interactive table of contents whith main chapter 
headings, which when you click on one displays a set of hyperlinks to 
relevant worksheets. For instance:

Worksheet 1, column A contains 10 text entries A1-A10

Each of the text entries in Worksheet 1 have 1-5 related text entries which 
are displayed in Worksheet 2 column B.

What I would like to do, is that when in Worksheet 1, you click on any of 
the A1-A10 entries, the "related" entries (displayed in Worksheet 2 column B) 
are displayed in Worksheet 1, column B.
0
MrNoJoy (1)
9/11/2007 8:10:02 AM
excel 39879 articles. 2 followers. Follow

1 Replies
365 Views

Similar Articles

[PageSpeed] 30

This is kind of inflexible, but should give you an idea of the logic.
I was trying to think of a FormulaArray method of doing this, but
figured since you're using VB anyway might as well do it all there.

Enter your table of content on Sheet 2 to repeat each value (A1:A10
from Sheet 1) and the corresponding values to match in column B on
Sheet 2.

Put this code in Sheet 1 code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
        Me.Range("B1:B5").ClearContents
        Dim i As Integer, j As Integer
        j = 1
        For i = 1 To
Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
            If Sheet2.Cells(i, 1).Value = Target.Value Then
                Me.Cells(j, 2).Value = Sheet2.Cells(i, 2).Value
                j = j + 1
            End If
        Next i
    End If
End Sub


On Sep 11, 4:10 am, Mr. NoJoy <MrNo...@discussions.microsoft.com>
wrote:
> Am sure this is really easy but the logic of it is escaping me. I'm trying to
> the following to make an interactive table of contents whith main chapter
> headings, which when you click on one displays a set of hyperlinks to
> relevant worksheets. For instance:
>
> Worksheet 1, column A contains 10 text entries A1-A10
>
> Each of the text entries in Worksheet 1 have 1-5 related text entries which
> are displayed in Worksheet 2 column B.
>
> What I would like to do, is that when in Worksheet 1, you click on any of
> the A1-A10 entries, the "related" entries (displayed in Worksheet 2 column B)
> are displayed in Worksheet 1, column B.


0
iasafiev (57)
9/12/2007 3:28:59 PM
Reply:

Similar Artilces:

supplier info confusion HELP!!!! :o)
Can somebody explain this .... I have an item on my DB which is supplied by 3 different suppleirs, but one ILC. If I run a sales report and group it by supplier, will it know how many i have sold from each supplier? And if so how does it know?! Thanks! I think the sale gets credited to the primary supplier on the detailed sales report...tried it on my laptop where I did the po and received the po for a secondary supplier assigned to the item, the detailed sales report credited the sale to the primary supplier....I looked at the transactionentry table and there is no reference to th...

Excel 2003 mis-displaying a value as repeating itself
When a user enters a "1" and exits the cell, the cell displays as "111111111111111111111". The cell is defined as Number, zero decimals, with (1234) formating. Any clues to why this may be occuring, or is there a solution ? Note: The spreadsheet was originally creating in Excel 2000. Hi maybe check 'Tools - autocorrection' -- Regards Frank Kabel Frankfurt, Germany J.D. Robbie wrote: > When a user enters a "1" and exits the cell, the cell > displays as "111111111111111111111". > The cell is defined as Number, zero decimals, wit...

Sorting groups of info
I need help sorting groups of information. I have about 15 groups of data each 5 columns wide and a length that varies between 5-10 rows. Each group's title is in the 3rd column. Is there any way that I can sort the groups of data ascending by their titles (without having to reformat the group of data? Thanks! Hi, Sue, If by "Each group's title is in the 3rd column." you mean that the title looks like a heading and is not embedded in the data, then I don't believe there's an easy way to sort the groups short of copying that title down in some out-of-the-way colu...

Outlook Today Folder Display
This problem started when I attempted to add folders to the Outlook Today display. Folders that I did not check became checked, and each time I tried to fix this another copy of Outlook Today ended up displayed on the page. I now have 7 Outlook Today entries and one Inbox plus two additional folders I selected. Any way to fix this? I'm running Windows XP with Outlook XP. This was a fresh installation on a new PC (all preloaded). Michael ...

Select Names displays ; or , for address
When I open the select names dialog box and add a name, it simply puts a ; or a , in where the address should be and when I go into the message after I'm done nothing shows either. Thanks, Chon Sounds to me like you imported these Contacts. Provide all the details on how you created them. -- Russ Valentine [MVP-Outlook] "Chon Nguyen" <nospam@nothanks.org> wrote in message news:#6QbvJhzDHA.4064@tk2msftngp13.phx.gbl... > When I open the select names dialog box and add a name, it simply puts a ; > or a , in where the address should be and when I go into the message a...

Can't restore main Outlook pst file info
After reformatting my hard drive etc. Outlook will not read the information in my main PST file. I have two others, Archive.pst and Hotmail.pst and these read ok. The main Outlook.pst only shows what looks to be the default folders and all are empty. I have tried the "detect and repair" program and tried delecting all the Outlook files and re-copying them from my back up disk. I am sure that the Outlook.pst file is ok, it's just that Outlook won't read it for some reason. Thanks for any help you can provide. ...

Lookup sheet names in formulas
What function or instruction do I use to lookup a sheet name in formula -- digica ----------------------------------------------------------------------- digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1492 View this thread: http://www.excelforum.com/showthread.php?threadid=27735 Hi see: http://www.xldynamic.com/source/xld.xlFAQ0002.html "digicat" wrote: > > What function or instruction do I use to lookup a sheet name in a > formula? > > > -- > digicat > ----------------------------------------------------------...

LOOKUP Help #7
Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot One way using non-array formulas is illustrated in this sample construct: http://www.savefile.com/files/720924 Placing source dates under correct month col....

How do I display a dialog whilst in OnInitDialog ?
I have a program which pastes a bit of information (read from a config file) into a listbox whilst in the OnInitDialog part of the program. The program then 'acts' on the data and part of it will change depending on those actions (network activity, so takes a few seconds). What I want the program to do is display the original data, then change the display to the new data, but at the moment it won't display the program until it's finished with the OnInitDialog part of the program, so you don't get to see the original data. All you see is nothing for a few seconds, th...

In MDI, how to display a restored view on a maxmized view?
In MDI, if a view is maxmized, all other views are hide. But I want to display a restored view on the top of the maxmized view, how can I do that? I am not sure if thats possible. You can try calling activateframe on the mdichild window that contains the view. I suspect that the new frame will come in as maximized as well. You can try by resizing the view to cover entire area of mainframe and then activating second frame. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "flyingxu" <flyingxu@discussions.microsoft.com> wrote in message news:20D25211-AC04-4E9A-A034-34F41A60B0F6...

Lookup Data in external workbook
Hello: I have a worksheet called "PlantAnalysis". Column A has the "Item ID" and I would like to put the description in column B; starting at cell B3. I am trying to lookup the Item Description from an external workbook called "Item master" on worksheet labelled "List". Column A has the "Item ID" and Column C has the "Description".Is VLookup the recommended way to do this? Below is what I have but can't get it to work. Sub getdesc() 'Inserting vlookup statement to populate material description ...

managing lookup table rebuilds
I am using a Delphi program to rebuild a lookup table from a source table imported (via an SSIS package) from a linked server. The catch is that the imports can happen throughout the day, so that the lookup table would often have to be built while users are accessing it via the front end app (also written in Delphi). My idea is to name the new lookup table by appending the date & time, then drop and recreate a synonym to point to it. I'm thinking that this will cause no locks because a running query will point to the old table during the DROP/CREATE SYNONYM. There may ...

Images not displaying on page
Hi, Apologies if this is a frequently asked question... PC has Publisher 2003 + SP1 (11.6255.6408) on WinXPPro + SP2, 4Gb RAM Problem I'm having is when I try to open a Publisher document which was not created on the above PC i.e. sent to me from someone else, or created by me on my previous PC. Specifically, any images do not display on the page, or in Print Preview. However, they do appear in the Graphics Manager if I click the Show Thumbnail checkbox, and they do print correctly. If I click View, Pictures on the menubar, the dialog is set to Detailed Display. If I change this, ...

Hyperlink points correctly, doesn't display right
I have a search routine in my workbook that finds the name of a document from an active cell on another worksheet, then inserts a hyperlink to that document. The hyperlink often does not change to reflect a new document - yet, when moused over or clicked, it's pointing to the correct document. I've tried F9 on the hyperlink cell, but it still doesn't change. Any suggestions? Ed Sorry - forgot my code. The data, including the doc name, is on Sheet1. The searc function is activated by a button on sheet "Get Doc". Sub FindTIR() Dim strLocation As String Dim ...

Meeting tracking info shows "none".
Environment: All Windows 2003 (server), Exchange 2003, XP, and Office 2003 (latest service packs and updates for all). We have a user that isn't seeing any response information under the tracking tab of a meeting they've created. It just says "none" and never updates, even though several users had already accepted. The problem still happens on other PC's where i create his profile. If i create my own profile on any of these same PC's, it works fine. Any ideas?? Thanks in advance. Is your client OPENING the responses that he receives? Does your client ha...

Info Required With Deleting Mail Box Stores, Help Please?
Quite a long time ago we had to re create our mail box stores and add the Mail boxes into these stores. This ment that we were left with the disused mail stores/faulty mail stores. We have tried to delete them but the following error comes up: "One or more users currently use this mail box store, These users must be moved to a different mail box store or be mail disabled before deleting this store" However all of the users are moved out of the stores and there are no accounts left at all. Has anyone else come across this problem before? If you have got a solution that would...

Pivot Table Form
Hi Everyone, I have a Pivot Table Form in Access 2003 and need the blank/null cells on the data axis to display "0". This is easy to do in Excel but I have searched through all of the options in Access and cannot find it. What is the VBA code required to set this option? Can anyone suggest a good reference book or website for coding Pivot Tables with VBA? Many thanks, David Hi David, can't you use the calculated value NZ([myvalue],0) instead of the value itself? -- Kind regards Noëlla "David Saywell" wrote: > Hi Everyone, > > I have a Piv...

How to lookup and display info?
Am sure this is really easy but the logic of it is escaping me. I'm trying to the following to make an interactive table of contents whith main chapter headings, which when you click on one displays a set of hyperlinks to relevant worksheets. For instance: Worksheet 1, column A contains 10 text entries A1-A10 Each of the text entries in Worksheet 1 have 1-5 related text entries which are displayed in Worksheet 2 column B. What I would like to do, is that when in Worksheet 1, you click on any of the A1-A10 entries, the "related" entries (displayed in Worksheet 2 column B) ...

Displaying cell references next to embedded cells in Word 2000
Hello Group. I've embedded some excel cells into my report written in Word 2000. Is there a way I can display the cell references next to the embedded object in the printout. For example if cells B10:C15 are the embedded cells, I want those who read the printed document to see exactly which rows and which columns the numbers belong to. Something like this: B C 10 Jan 10% 11 Feb 15% 12 Mar 34% 13 Apr 14% 14 May 10% 15 Jun 12% So when I say somthing like "The formula used here is C10/SUM(C10:C15)" my readers would be able to refer to the ...

Frogs in the pond
Hi, I have this little charting problem. I'm sure someone will have the solution. It's difficcult to explain so I 've posted a picture here www.fransysco.net/frogs/frogs.gif You may have to zoom on your browser to see it perfectly. I have some data in a table and it's figures about frogs in a a pond and about how many arrive in each batch per quarter and how long they survive. The length of survival is measured in Quarters, too. So each quarter there's a batch of new frogs that arrive - usually 2000 in a a batch. And I measure the survival rate of each batch. Now, if I al...

LED light box display--Only 11 mm LED slim light box in China! 05-24-07
Edlite is dedicate to develop comprehensive technology on advertising light-box including LED; CCFL; EEFL and FLUORESCENT series.we already sell numerous light boxes around the world for tradeshow booth or display purpose. More detail.please enter into www.edlite.hk,or send mail to jenny@edlite.hk,or add MSN:madamenihao@hotmail,then we would reply to you at the first time. ...

Version, language and SP info
I'm developping an ActiveX-component for Outlook 9, 10 and 11. However, due to a bug in Outlook 10, Dutch, Pre-SP1 it won't work in this environment (KB: #310571). I would like to show the user a message urging him to upgrade to SP1+ I'f found the Application.Version field, this only shows the version. The LanguageSettings-object is unfortunately not documented on MSDN, but may provide the language. Is there a property to show the installed Servicepack? You have to use the minor parts of the version string to see what build is running and use that to decide on the SP level. Sea...

What is "more" displayed at tge bottom of my histogram data?
I am constructing a histogram and after I use the data analysis there is a new bin at the bottom labeled "more." I have no idea what this means or why it is there, but it contains a frequency number. Please help me. Does it count observations above the last bin value? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "lassy" <lassy@discussions.microsoft.com> wrote in message news:472E6CB9-399B-4E71-9EA6-077C9B741415@microsoft.com... >I am constructing a histogram and after I use the data analysis the...

Change Display Units property with VBA
Hello, I would like to dynamically change the units on my graph within my powerpoint presentation using VBA. The Powerpoint group has site has been down for a couple of days so I have not been able to send my question in. I would think the code might be similar to that in excel so I'm hoping someone can help me here. Can someone help some code that allows me to change the "Display Units" property found under the "Format Axis --> Scale" menu in the graphs? thank You, Jenise In article <1168541545.394773.220220@o58g2000hsb.googlegroups.com>, jwilliam@alte...

Some Cells display text others dates in the same column
I imported a .csv file into Excel. One column contains the SKU # of a product. All my SKU's are formatted as x-xxxx. However some of the cells in the column display the SKU as it should, but many (45 out of 170) are displayed as a date format mmm-yy. Each one of the 45 is different. The cells in question show up as "custom" and when I try to change it to "text" I get a completey different number than the SKU I need. Somebody please help. Hi Phil, Format the entire column manually, choose Format, Cells, Number tab, Custom and enter #-#### in the Type box...