MATCH function return second result

[thesnowplow]

=OFFSET(Sheet1!$A$7,0,MATCH(Sheet1!$A$1,Sheet1!$B$6:$OI$6,0),200,1)

Formula I use as a named range for my chart series works perfectly. How do I modify it to return 2nd result as there are more than one matches that fit the criteria $A$1.

 

[Domenic]

Actually, rather than leaving that column empty, it may be better to enter #N/A values for your range in that column.

 

[thesnowplow]

I just tried and it worked. I will test it out on the real thing tomorrow at work but thank you very much for your help!

This will save massive amounts of time for generating multi graphs going forward.

 

[thesnowplow]

Hey man I want to give you an update as your help was incredible.

See picture attached, it's a chart sheet. Imgur: The most awesome images on the Internet

I combined your formulas and modified them with one more variable so I ended up with 2 drop down boxes, the second one is dynamic displaying all suppliers for each selected product. It works like magic. I can slice 400-500 columns with a click of a button and it doesn't require much maintenance from week to week.

Question: because my X axis is based on also a dynamic column, for it to display days properly 1 at a time I have to "Specify interval unit:" (in picture its 125). Is there a way to link that field through VBA perhaps to a cell A1 in Sheet1 for example? I tried googling but couldn't find anything..

 

[Domenic]

For your new question, I suggest you start a new thread...

 

[Domenic]

You can use a worksheet change event to automatically change the interval unit. So, let's say that cell A1 in Sheet1 will be used to specify the interval, and that the same sheet, Sheet1, contains the chart, right-click the sheet tab for Sheet1, and select View Code. Then paste the following code in the code module for the sheet...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address <> "$A$1" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(Target) = 0 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsNumeric(Target) [COLOR=darkblue]Then[/COLOR]
        Target.Select
        Target.ClearContents [COLOR=green]'optional[/COLOR]
        MsgBox "The interval must be a number.", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]If[/COLOR] Target.Value <= 0 [COLOR=darkblue]Then[/COLOR]
        Target.Select
        Target.ClearContents [COLOR=green]'optional[/COLOR]
        MsgBox "The interval must be a whole number greater than 1.", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] If
    Me.ChartObjects("Chart 1").Chart.Axes(xlCategory).TickLabelSpacing = Target.Value
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

If your chart is located in another sheet, let's say Sheet2, replace...

Me.ChartObjects("Chart 1").Chart.Axes(xlCategory).TickLabelSpacing = Target.Value

with

Worksheets("Sheet2").ChartObjects("Chart 1").Chart.Axes(xlCategory).TickLabelSpacing = Target.Value

Hope this helps!