Posts by deutz

Re: AND and OR for Advanced Filter range on large number of ORs

Thanks rory,

That's what I figured. Sometimes you have the solution right under your nose.

Regards
deutz

Re: AND and OR for Advanced Filter range on large number of ORs

After mucking about with this for a while it appears that the easiest solution was simply to build a formula with COUNTIFS and place it in a cell on the data sheet and run the advanced filter off that formula rather than try to figure out what rows and columns to place each piece of the criteria into.

Regards
deutz

Hi and thanks in advance,

I'm using Excel 2010 and trying to build a userform that uses AND and OR on a number of data columns and then using this criteria range on an Advanced filter. I have one sheet with the data and the criteria range off to the top right of the data and then on another sheet, I output the filtered data. This works well for a fairly basic set of ANDing or ORing criteria, where there are just two rows of critieria (so just the one OR). I understand that you AND everything on a single criteria row and that ORs are applied between the rows but wondering how that is represented on the sheet in the case of ORing a larger number of values. How would you output the criteria to the sheet for the Advanced filter?

For instance,

Suppose I had criteria that I wanted to OR for each of 3 or more columns and then AND each set of ORed criteria ...

So if you were writing a formula, the logic would be something like:

(Below I've just included 4 criteria for each column as an example, but there could be more or less criteria for each column and more or less columns overall)

AND(OR(Col1="Dog",Col1="Cat", Col1="Rat", Col1="Bat", ...),OR(Col2="A", Col2="B",Col2="C",Col2="D",...),OR(Col3="1",Col3="2",Col3="3",Col3="4",...))

Thanks
deutz

Re: Hide one group and show another for custom ribbon

Thanks Carim, I was able to get it working using Ron's code with one small modification where I test for the workbook name in the RibbonOnLoad sub and refresh the visibility at that point. Thanks rory for your suggestion to post the workbook but I don't have ownership of it so unable to do that and the code I posted is all the code that deals with the ribbon.

Dim Rib As IRibbonUI
Dim MyTag As String
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
    If ThisWorkbook.Name = ORIGINAL_WKB_NAME Then
        Call RefreshRibbon(Tag:="BuildDiary")
    Else
        Call RefreshRibbon(Tag:="Diary")
    End If
End Sub
Sub GetVisible(control As IRibbonControl, ByRef visible)
    If control.Tag Like MyTag Then
        visible = True
    Else
        visible = False
    End If
End Sub
Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If Rib Is Nothing Then
        MsgBox "Error, restart your workbook"
    Else
        Rib.Invalidate
    End If
End Sub

Regards
deutz

Hi and thanks in advance,

I am working in Excel 2010 and have a custom ribbon with 1 tab that has 2 groups with buttons. I would like to hide one group and show the other group or visa versa, depending on the name of the workbook (name is compared to the value in the constant ORIGINAL_WKB_NAME).

In the XML for each group of buttons I have used getVisible like this ...

<group  id="grpTeamLeave" getVisible="GetVisible" tag="Diary">


<group  id="grpBuildDiary" getVisible="GetVisible" tag="BuildDiary">

In a standard module I have this code to try and toggle the visibility of the 2 groups depending on the name of the workbook but the ribbon tab is not showing either group regardless of the workbook name ...

Private myRibbon  As IRibbonUI
Private myTag     As String
Private myState   As Boolean


Sub ribbonOnLoad(ribbon As IRibbonUI)
    Set myRibbon = ribbon
    Call InitRibbon
End Sub


Sub RefreshRibbon(Tag As String)
    myTag = Tag
    myRibbon.Invalidate
End Sub


Sub GetVisible(control As IRibbonControl, ByRef visible)
    If control.Tag Like myTag Then
        visible = myState
    End If
End Sub


Sub InitRibbon()
   If ThisWorkbook.Name = ORIGINAL_WKB_NAME Then
        myState = True
        Call RefreshRibbon(Tag:="BuildDiary")
        myState = False
        Call RefreshRibbon(Tag:="Diary")
    Else
        myState = True
        Call RefreshRibbon(Tag:="Diary")
        myState = False
        Call RefreshRibbon(Tag:="BuildDiary")
    End If
End Sub

Not sure what is going on here?

Thanks
Deutz

Re: Dynamic VLOOKUP to return variable number of columns

Thanks Carim for your excellent answer.

I think perhaps I was a tad ambitious trying to get it all into one formula. The way you have broken it down into steps is easy to understand and probably will be easier to modify if I have to add columns in the future.

Thanks again
Cheers
Deutz

I am trying to come up with a formula to go in column G of the example DATA sheet that will concatenate the 0 or 1 values in columns A & B & C of the DATA sheet and use that concatenated result to match on the 1^st column in the LOOKUP sheet and return the value in the 2^nd column of the LOOKUP sheet which is a list of one or more columns in the DATA sheet to return values from. If there is more than one column returned from the LOOKUP sheet, I want to separate the values from those columns with commas, as per my example.

I have the Concatenate bit working and the VLOOKUP from the Lookup table working in the case where there is just a single column to output but not sure how to do this if there is more than one column to output without the formula getting real ugly.

This is what I have so far for the formula in cell G1 of the DATA sheet which returns the value in column D of the DATA sheet:

=INDIRECT(VLOOKUP(CONCATENATE(A1,B1,C1),LOOKUP!$A$1:$B$7,2,FALSE)&ROW())

[TABLE="class: grid, width: 500"]

[TD="colspan: 7"]DATA sheet[/TD]

A

B

C

D

E

F

G

0

0

1

Bat

Dog

Cat

Formula should output: Bat

1

1

0

Frog

Mouse

Lemur

Formula should output: Frog, Lemur

0

1

1

Horse

Fly

Lion

Formula should output: Horse, Fly, Lion

1

1

1

Bat

Rat

Turkey

Formula should output: Bat, Rat, Turkey

[/TABLE]

[TABLE="class: grid, width: 500"]

[TD="colspan: 2"] LOOKUP sheet[/TD]

Value to match on

[TD="width: 113"] Cols to ouput to col G in data sheet[/TD]

[TD="width: 102"] A[/TD]
[TD="width: 113"] B[/TD]

[TD="width: 102"] 001[/TD]
[TD="width: 113"] D[/TD]

[TD="width: 102"] 010[/TD]
[TD="width: 113"] E[/TD]

[TD="width: 102"] 100[/TD]
[TD="width: 113"] F[/TD]

[TD="width: 102"] 110[/TD]
[TD="width: 113"] D, F[/TD]

[TD="width: 102"] 101[/TD]
[TD="width: 113"] D, E[/TD]

[TD="width: 102"] 011[/TD]
[TD="width: 113"] D, E, F[/TD]

[TD="width: 102"] 111[/TD]
[TD="width: 113"] D, E, F[/TD]

[/TABLE]

Thanks in advance

Deutz

Re: Formulas to count duplicate rows

Quote from MrRedli;778129

hope this helps...

Thanks for your efforts and I can see you have the correct results using a helper column. I am wondering if you can achieve the same thing without a helper column, in a single formula for both examples?

Re: Formulas to count duplicate rows

forum.ozgrid.com/index.php?attachment/70293/

Quote from MrRedli;778121

can you attached a sample file?

Attached are basic examples of the two results I am trying to achieve. Hope it is clear.

Thanks

Re: Formulas to count duplicate rows

I removed the IF statement and added the columns but I'm afraid it only counts the number times that row 2 appears. So not sure if I am missing something here?

thanks

Re: Formulas to count duplicate rows

Hi MrRedli,

Thanks for your suggestion but that formula will only flag if row 2 is a duplicate row and not count the total duplicate rows for the sheet. I would ideally like to do this all in one formula if possible, perhaps an array formula or similar, without dragging a row specific formula down a helper column and then having to sum it to get the count.

Regards
Deutz

Hi and thanks in advance,

I'm wondering how to build a couple of formulas that don't use a helper column/concat column. One formula should count duplicate rows (on a single sheet) including the first occurrence of the duplicate. The second formula should count duplicate rows (on a single sheet) and NOT include the first occurrence of the duplicate. The sheet can have multiple columns.

Thanks
Deutz

Re: Activate custom ribbon tabs for differents sheets

I just put this line of code in the RibbonOnLoad callback and that solved the problem when the workbook opens.

ThisWorkbook.Worksheets("Estimate").Select

Thanks for your help and suggestions.

Regards
deutz

Re: Activate custom ribbon tabs for differents sheets

Quote from rory;752978

Assuming you have stored a pointer to the ribbon in the RibbonOnLoad callback, you need to use the ActivateTab method to switch tabs, called from the relevant Worksheet_Activate event.

Ok, I think I am close to getting this working. First I had a typo in my code with tabActivate instead of ActivateTab. When I fixed that the ribbon tabs activate when the sheets are selected as required. The only issue seems to be when the workbook is first opened because the RibbonOnOpen sub in the module runs AFTER the first Worksheet_Activate event so the Rib variable = Nothing with the first call to the ActivateTab method. Not sure how to get around this?

Re: Activate custom ribbon tabs for differents sheets

Quote from pike;752987

Andy Popes add-in makes it easier to design ribbons
http://www.andypope.info/vba/ribboneditor_2010.htm

Thanks for the link. That is a very nice add-in.

Re: Activate custom ribbon tabs for differents sheets

Quote from rory;752978

Assuming you have stored a pointer to the ribbon in the RibbonOnLoad callback, you need to use the ActivateTab method to switch tabs, called from the relevant Worksheet_Activate event.

Hi rory. I found this code but can't seem to get it to work.

I put this in a standard module ...

Public Rib as IRibbonUI
Sub RibbonOnLoad (ribbon As IRibbonUI)
Set Rib = ribbon
End Sub

Then i put this code in the Worksheet_Activate event for the Estimate worksheet but get the error: "Object variable not set". So don't know if I need to somehow call the first bit of code to set the variable Rib or something?

Call Rib.tabActivate("tabEstimate")

Thanks

Re: Activate custom ribbon tabs for differents sheets

Quote from rory;752978

Assuming you have stored a pointer to the ribbon in the RibbonOnLoad callback, you need to use the ActivateTab method to switch tabs, called from the relevant Worksheet_Activate event.

Thanks rory. I'll see if I can get it working.

Hi and thanks in advance,

I am using Excel 2010 and have created a custom ribbon with two tabs and there are two worksheets. When you activate the Quote worksheet the tabQuote tab shows and the tabEstimate tab is hidden. When you activate the Estimate worksheet the tabEstimate tab shows and the tabQuote tab is hidden. The other Excel built in tabs are visible at all times. Both of my custom tabs are set to appear before the Home tab. When either of the custom tabs are shown I want them to be the active tab which is not the case if one of the Excel built in tabs was the active tab prior to showing or hiding my tabs. Here is the custom ribbon xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">  <ribbon startFromScratch="false">    <tabs>      <tab id="tabEstimate" label="Estimate" insertBeforeMso="TabHome" getVisible="GetVisible" tag="Xestimate">        <group id="customGroup1" >          <button id="customButton1" label="Clear" size="large" onAction="EstimateClear" imageMso="Clear" />    <separator id="MySeparator1" />          <button id="customButton2" label="Print" size="large" onAction="EstimatePrint" imageMso="PrintMenu" />   <separator id="MySeparator2" />          <button id="customButton3" label="Export" size="large" onAction="EstimateExport" imageMso="ExportExcel" />  </group>     </tab> <tab id="tabQuote" label="Quote" insertBeforeMso="TabHome" getVisible="GetVisible" tag="Xquote">        <group id="customGroup2" >          <button id="customButton4" label="Clear" size="large" onAction="QuoteClear" imageMso="Clear" />   <separator id="MySeparator3" />          <button id="customButton5" label="Print" size="large" onAction="QuotePrint" imageMso="PrintMenu" />   <separator id="MySeparator4" />          <button id="customButton6" label="Email PDF" size="large" onAction="QuoteEmailPDF" imageMso="CreateEmail" />   <separator id="MySeparator5" />   <button id="customButton7" label="Export" size="large" onAction="QuoteExport" imageMso="ExportExcel" />  </group>     </tab>    </tabs>  </ribbon></customUI>

Re: Named Range message when copying sheet

Quote from royUK;698260

What solved it?

Replacing the named range with a direct reference to the sheet thusly: =Sheet1!$A$1:$A$3

Thanks again.

Re: Named Range message when copying sheet

Thankyou royUK and chirayuw,

That solved it!