click one cell will generate result/name.

Hello,

If you want to do this with a macro, try something along these lines:

Please Login or Register  to view this content.

You need to create range names for each game, i.e.

Badminton =Sheet1!$I$6:$I$9
Bowling =Sheet1!$K$6:$K$10
Tennis =Sheet1!$J$6:$J$9
showList ="Tennis" -- this is a starting value and will be changed by the macro.

... and this formula in column D

=IFERROR(INDIRECT(showList),"")

If you don't want to use VBA, you will need a helper cell where you select the game, instead of clicking the "click to view" cell. The formula will then be

=IFERROR(INDIRECT($G$4),"")

where G4 is the cell where you select the game.

See attached for both options working.

cheers,

Maybe you want to look at the possibility of using Dynamic Dependent Lists?

1/. Create Static/Dynamic Named Ranges
Name/Formula
GAMES

Please Login or Register  to view this content.

Headers

Please Login or Register  to view this content.

BADMINTON

Please Login or Register  to view this content.

TENNIS

Please Login or Register  to view this content.

BOWLING

Please Login or Register  to view this content.

2/. Apply Data Validation to B2:B4
Allow:=
List
Source:=

Please Login or Register  to view this content.

Apply Data Validation to C2:C4
Allow:=
List
Source:=

Please Login or Register  to view this content.

3/. For more information on dynamic names, and alternative methods for creating them, click the link below
Contextures - Excel Data Validation - Create Dependent Lists

Select from the drop-downs in B2:B4 to see the changes in the lists in C2:C4
Hide the lists with the grouping +/- button.

N.B. the cells C2:C4 will not automatically clear when a new selection is made in B2:B4 .
For this we need VBa using the worksheet change event

hye,

thanks all for the help, but i got more confused because I'm not familiar with vb/macro/etc
I made one workbook, what I try to do is, whenever I click "Click to view" , it will listed all the details which i saved in sheet "contract" to the Contract Table.

Would u guys help me?

Sorry, but I don't see what to change.

You have received several working solutions in response to this question, which you posted in triplicate.

There are suggestions with formulas and helper cells. You rejected them as "that's not what I want".

I've shown you a solution that does exactly what you want. You've rejected it with "I'm not familiar with VBA".

Well, it'll be one or the other. We cannot make Excel do what you want in the manner that you expect, and make all that work to a level of complexity that also matches your expertise.

It actually works the other way. You've received three or four different working suggestions. Now you need to get a grip on your Excel skills and be ready to expand them. That's called "learning" and it applies whenever you stretch yourself to understand something you did not know before.

Just whining that you don't have the skills to apply the working VBA, and you also don't want to apply the formula/helper cell solutions, won't cut it.

Stomping your foot when Excel does not behave the way you want it to is certainly an option. But I'v never really seen this technique produce any useful results.

Have it your way, and dismiss or ignore the suggestions here. In any case, please do not use Private Messaging or Visitor Messaging to discuss your questions again.