how display info from sheet 2 and display on sheet 1

josephg · #1 (**permalink**) 02-02-04, 20:47

How can I diplay information for user from sheet2 on sheet1 cell C by cliking on user name on sheet 1

Sheet 1
Joe 987651
john 554654
Steve 876768
mark 65656

Sheet 2
Name joe
Address 22 oxford st
Phone 987651

Name mark
Address 2 Qeen st
Phone 65656

Name Steve
Address 2 king st
Phone 876768

Name john
Address 23 adndree
Phone 554654

Smitty · #2 (**permalink**) 02-03-04, 00:41

Maybe hyperlink with VLOOKUPS?

It's not really clear how you have things set up, bu from your example it would seem easier to use Excel's native format and not split your information with blank rows.

Smitty

shades · #3 (**permalink**) 02-03-04, 08:43

Or you could put something like this into the View Code as a Selection Change. This assumes that the column for selection is Column A. By selecting any cell between 4 and 70 in Column, it will take the user to the "Wahtever" named range.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Column < 2 Then
        If ActiveCell.Row < 4 Then
            Exit Sub
        ElseIf ActiveCell.Row > 70 Then
            Exit Sub
        Else
            Sheets("Sheet1").Range("A2") = ActiveCell.Value

            Application.Goto Reference:="Whatever"  '(defined name, refering to a specific cell).

        End If
                
    End If

End Sub

josephg · #4 (**permalink**) 02-03-04, 19:06

Thank you for your help, I tried your code but it did not worked
I am attaching the Excel sample file with the code you have supplied but it does not work, could you please assist

Dcutler · #5 (**permalink**) 02-04-04, 14:38

Look at the attached sample.

I would recommend putting all the data, including your list of Names & #'s on sheet2, organized in rows (ie joe in A1, address in B1, # in C1).
(which I have done in sample).
You can use a combo box to select the name/# combination(should put on Sheet!2 also), put the selection in a cell under the box, then use Vlookup to bring in the data for that person you want. You can have each field be in its own cell, or concatenate into one cell(I did it this way).

josephg · #6 (**permalink**) 02-04-04, 17:48

Dcutler

It is not possible to put the data in in rows, because the data file for each customers are more that 3 lines and varies by customers some times more than 20 lines.
I must say customers data file are separted by new line as I have shown you in my sample file

Dcutler · #7 (**permalink**) 02-06-04, 08:30

Is the same kind of data always in the same row, i.e., is the name always in Row 1, Address Row 2, etc?

If there is missing data, i.e., there is no address, does that come through as a blank row?

What is the source of the data? Is it manually keyed into this excel sheet, or does it originate from an outside database or program?
If so, what program or database is it coming from?

josephg · #8 (**permalink**) 02-09-04, 01:14

Answer to you questions:
The data file is exported to sheet2
If there is missing data, it does not come in blank raw, the next raw is phone,
example if address2 is missing for name mark then we get
Name mark
Phone 65656

If the phone raw is missing, simply there is empty line and after that there is another name with address and phone
Example
Name mark
Address 2 Qeen st

Internet Services

Web Development

Digital Marketing

Consumer Tech