Follow steps and try this. Hope this will work for you: 1. *Right Click on Cell C5* 2. *Create a hyperlink with cell refrence C5 in the same (output sheet )* 3. *Do same for Cell D5 and create hyperlink with cell refrence D5 in the same (output sheet )* 4. *Right click on output sheet and view code and copy-paste below code to get the output you want.*
* * *Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)* * * * Dim wksSht As Worksheet* * Dim rngRange As Range* * Dim varData() As Variant* * * * Const strOutPutShtName As String = "Output"* * Const strDataStartCell As String = "K7"* * Const strOutPutDataStartCell As String = "B9"* * Const lngTotalDataCol As Long = 5* * * * Set rngRange = Nothing* * On Error Resume Next* * Set rngRange = Worksheets(strOutPutShtName).Range(Target.SubAddress)* * On Error GoTo -1: On Error GoTo 0: Err.Clear* * If Not rngRange Is Nothing Then* * If Len(Trim(rngRange.Value)) > 0 Then* * Set wksSht = Nothing* * On Error Resume Next* * Set wksSht = Worksheets(Trim(rngRange.Value))* * On Error GoTo -1: On Error GoTo 0: Err.Clear* * If Not wksSht Is Nothing Then* * With wksSht* * Set rngRange = .Range(strDataStartCell)* * Set rngRange = rngRange.Resize(.Cells(.Rows.Count, rngRange.Column).End(xlUp).Row - rngRange.Row + 1)* * Set rngRange = rngRange.Resize(, .Cells(rngRange.Row, .Columns.Count).End(xlToLeft).Column - rngRange.Column + 1)* * If rngRange.Rows.Count > 1 Then* * Set rngRange = Intersect(rngRange, rngRange.Offset(1))* * varData = rngRange.Value* * With Worksheets(strOutPutShtName)* * Set rngRange = .Range(strOutPutDataStartCell).CurrentRegion* * Set rngRange = Intersect(rngRange, rngRange.Offset(1, 1))* * rngRange.ClearContents* * rngRange.Resize(1, 1).Resize(UBound(varData), UBound(varData, 2)).Value = varData* * End With* * End If* * End With* * End If* * End If* * End If* * * * Set wksSht = Nothing* * Set rngRange = Nothing* * Erase varData* * * *End Sub* On Tuesday, 16 April 2013 12:14:42 UTC+5:30, amar takale wrote: > > Dear Chaya > Thanks very much for Struggle for my Solution but yet not got output as I > wish.You know that Yesterday I attached Sheet (Final+3) for Remove NA error > in that sheet I want click Option for output not Data validation.I attached > sheet here.Pls see it > Regards > Amar > > > > > > On Tue, Apr 16, 2013 at 11:48 AM, Chaya <chaya...@gmail.com > <javascript:>>wrote: > >> Dear Amar, >> >> PFA... Like this you are expecting right ? >> >> Regards, >> Chaya >> >> >> >> On Tue, Apr 16, 2013 at 11:43 AM, amar takale >> <amart...@gmail.com<javascript:> >> > wrote: >> >>> Dear Chaya >>> >>> It's working good & Good one attempt also useful for me. But exact which >>> I want to solution not attempt.I want only click on criteria name & then >>> show data as like any webpage show.So Pls try to understand me & give >>> Only click solution. >>> >>> Regards >>> Amar >>> >>> >>> On Tue, Apr 16, 2013 at 11:05 AM, Chaya <chaya...@gmail.com<javascript:> >>> > wrote: >>> >>>> Dear Amit, >>>> >>>> PFA, i hope it will meet your requirement. >>>> >>>> >>>> Regards, >>>> Chaya >>>> >>>> >>>> >>>> >>>> On Tue, Apr 16, 2013 at 10:36 AM, amar takale >>>> <amart...@gmail.com<javascript:> >>>> > wrote: >>>> >>>>> Dear Experts >>>>> I want help all our members of group who solved my issue of Click >>>>> option. >>>>> We also use Data validation.But I required Click option on criteria. >>>>> >>>>> Appreciate the help >>>>> >>>>> Regards >>>>> Amar >>>>> >>>>> -- >>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>>> https://www.facebook.com/discussexcel >>>>> >>>>> FORUM RULES >>>>> >>>>> 1) Use concise, accurate thread titles. Poor thread titles, like >>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need >>>>> Advice will not get quick attention or may not be answered. >>>>> 2) Don't post a question in the thread of another member. >>>>> 3) Don't post questions regarding breaking or bypassing any security >>>>> measure. >>>>> 4) Acknowledge the responses you receive, good or bad. >>>>> 5) Jobs posting is not allowed. >>>>> 6) Sharing copyrighted material and their links is not allowed. >>>>> >>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners >>>>> and members are not responsible for any loss. >>>>> --- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "MS EXCEL AND VBA MACROS" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to excel-macros...@googlegroups.com <javascript:>. >>>>> To post to this group, send email to >>>>> excel-...@googlegroups.com<javascript:> >>>>> . >>>>> Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>>>> For more options, visit https://groups.google.com/groups/opt_out. >>>>> >>>>> >>>>> >>>> >>>> >>>> >>>> -- >>>> >>>> Regards, >>>> >>>> Chaya >>>> >>>> -- >>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>> https://www.facebook.com/discussexcel >>>> >>>> FORUM RULES >>>> >>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please >>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>>> will not get quick attention or may not be answered. >>>> 2) Don't post a question in the thread of another member. >>>> 3) Don't post questions regarding breaking or bypassing any security >>>> measure. >>>> 4) Acknowledge the responses you receive, good or bad. >>>> 5) Jobs posting is not allowed. >>>> 6) Sharing copyrighted material and their links is not allowed. >>>> >>>> NOTE : Don't ever post confidential data in a workbook. Forum owners >>>> and members are not responsible for any loss. >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "MS EXCEL AND VBA MACROS" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to excel-macros...@googlegroups.com <javascript:>. >>>> To post to this group, send email to >>>> excel-...@googlegroups.com<javascript:> >>>> . >>>> Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>>> >>>> >>> >>> -- >>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>> https://www.facebook.com/discussexcel >>> >>> FORUM RULES >>> >>> 1) Use concise, accurate thread titles. Poor thread titles, like Please >>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>> will not get quick attention or may not be answered. >>> 2) Don't post a question in the thread of another member. >>> 3) Don't post questions regarding breaking or bypassing any security >>> measure. >>> 4) Acknowledge the responses you receive, good or bad. >>> 5) Jobs posting is not allowed. >>> 6) Sharing copyrighted material and their links is not allowed. >>> >>> NOTE : Don't ever post confidential data in a workbook. Forum owners and >>> members are not responsible for any loss. >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "MS EXCEL AND VBA MACROS" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to excel-macros...@googlegroups.com <javascript:>. >>> To post to this group, send email to excel-...@googlegroups.com<javascript:> >>> . >>> Visit this group at http://groups.google.com/group/excel-macros?hl=en. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >>> >>> >> >> >> >> -- >> >> Regards, >> >> Chaya >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> 2) Don't post a question in the thread of another member. >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> 4) Acknowledge the responses you receive, good or bad. >> 5) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros...@googlegroups.com <javascript:>. >> To post to this group, send email to excel-...@googlegroups.com<javascript:> >> . >> Visit this group at http://groups.google.com/group/excel-macros?hl=en. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
