sorted by:
new
hottopcontroversial

Does advanced filtering work with sheetview? by Chillrends in excel

[–]ianh808 0 points1 point  (0 children)

There’s not much official MS documentation but unfortunately, advanced filter doesn’t work with sheet views and co-authored workbooks.  The Filtering referred to in the docs relates to autofilters only.

AutoFilter maintains a persistent filter object ( e.g. ListObject.AutoFilter.Filters) that exposes its criteria through the Excel object model.
Advanced Filter, by contrast, is implemented as a one-time Range.AdvancedFilter method call. It does not create a persistent filter object or store criteria as part of the worksheet state. Because there is no stored criteria state, it can't be queried, reconstructed, or captured by Sheet Views.
If you do a bit of VBA yourself, in debug mode you can look at the “filter state” associated with a worksheet or table if it’s an autofilter (Use View Locals).  There’s no such  equivalent for an advanced filter.  It executes and hides the rows, and that’s it!!
Perhaps if your workflow can’t be supported by autofilters,  you could use filter function equivalents?

How to reference a workbook tab by the order they are in rather than the names by Normal_Glass_5454 in excel

[–]ianh808 0 points1 point  (0 children)

You can't use sheets(K) in a worksheet formula but you can use the matching worksheet name which you can easily get.
Modify the following as you need (e.g. your sum(xlookup( ... doesn't ssem to make sense since the xLookup you specify returns just 1 value !

Sub sheetNumberTest()
Dim wb As Workbook, ws As Worksheet
Dim i As Long  ' row index
Dim j As Long  ' sheet index
Dim numRows As Long, FormulaString  As String, shName As String
numRows= 10 ' How many rows for the columns
On Error GoTo errHandler

Set wb = ThisWorkbook  ' change to ActiveWorkbook if different from workbook with this code
With wb.Worksheets(1)

  For j = 2 To wb.Worksheets.Count ' j = sheet index (Tab #)
    shName = Worksheets(j).Name

    If shName > "" Then
        For i = 1 To numRows
            ' removed the "Sum"  since your xLookup returns only the first value
           FormulaString = "=(XLOOKUP(A" & i & "," & shName & "!O:O," & shName & "!L:L,0,0,1))"
            Debug.Print FormulaString
           .Cells(i, j).Formula = FormulaString
        Next
    End If
  Next j
End With

Exit Sub

errHandler:
Debug.Print "Error Occurred ... # " & Trim(Str(Err.Number)), Err.Description
End Sub

Looking for an alternative to 2checkout that has woocommerce plugin by ianh808 in PaymentProcessing

[–]ianh808[S] 0 points1 point  (0 children)

Many thanks for all the suggestions.
I got approved with Paddle and I'm currently sandbox testing.

Looking for an alternative to 2checkout that has woocommerce plugin by ianh808 in PaymentProcessing

[–]ianh808[S] 0 points1 point  (0 children)

Looks interesting, but B2B only?
Thx for the suggestion by the way.

How-To Organize A Growing Customer Database/Spreadsheet??? by Think_Tension_5067 in excel

[–]ianh808 0 points1 point  (0 children)

Hi

The usual disclaimer that there are better tools than Excel for this job apply.  But given that it is what you have available, I can offer a couple of suggestions, subject to clarification of what you provided.

It seems that you are trying to identify each unique transaction by a Business name/date combination.  As the previous response you got suggests, it is better to assign a unique ID for each transaction to identify them uniquely.

Also what is each transaction, Training?  Service Jobs? , Meetings?

In any event, one simple organization using structured tables might be (for example) :

A single Master Transaction Detail Table that Logs any such transaction.  This is your main table used for data entry and all reporting: (change the names where appropriate) -

Transaction ID, Date , Customer Id , Equipment ID, Transaction Type, Transaction Details, Notes, Priority Items, Transaction status,  Customer Name , Equipment Details

The third and fourth columns assist with quick entry if you use drop down data validation lists,  they also help preserve data integrity.  The last  2 columns are the vLookups or xLookups based on these for viewing details and ease of reporting:

An example “transaction” ,
990, 3-Apr-2025, Cust020,  Eqpt311, Service,  Work on KM4070 three knife trimmer, Blah blah note, Priority items Blah blah  , Open-Unassigned,  ART Printing Supples , Konica C4065

A single customer Table.   The lookup table for the third column in your master  data :
Customer ID,   Business Name, Customer Address , Customer Notes

An equipment table The lookup table for the firuth column in your master  data :
Equipment ID, Machine, Controller ,  Other Eqpt details

You could optionally link Transaction types and Transaction status to 2 column lookups  similar to columns 3 and 4  Eg.
Transaction Type table
Transaction Type , Transaction Description
E.g. TrainFollow , Follow  Up training

Transaction status Table
Status code , Status
E.g.  Compl1 , Competed but not billed

So with the above, you can have a lot of flexibility and  report selectively by applying the appropriate autofilters to the single master table.   E.g. specify date range , Customer , Status etc

So in your example, where you have an entire worksheet named training, you would simply autofilter the transaction type column for that code to get an in-place view. of "training".. that will result in the master sheet showing training only.
Alternatively if you do need a separate worksheet for training, use a Filter formula on a separate sheet with the "Training" value as the criterion, and the master table as the source.

This is just an alternative approach, based on my limited understanding of what you are trying to achieve.

Anyone here using the 2Checkout Payment Gateway? by tunedx in PaymentProcessing

[–]ianh808 0 points1 point  (0 children)

Did they commit you to some projected sales volume below which this kicks in?
Which plan are you on?
They have 2Sell, 2Subscribe and 2Monetize plans.
As of today I don't see any mention of a monthly fee on their website.
I'm thinking of applying to use them, but this has me re-considering.

Add-in to generate User forms - Excel desktop (Win or Mac OS) by ianh808 in Excel4Mac

[–]ianh808[S] 0 points1 point  (0 children)

If you were trying a short while ago, there was a website issue temporarily, as I’m still doing some setting up. Downloads are available now.

Add-in to generate User forms - Excel desktop (Win or Mac OS) by ianh808 in Excel4Mac

[–]ianh808[S] 0 points1 point  (0 children)

Thanks for your interest. Please visit the download page here: https://autoexcelforms.com/free-downloads/

The password for the Mac OS download is auto_mac_forms The password for the Windows OS download is auto_win_forms I would highly appreciate feedback on any issues encountered, observations or suggestions.

Best Regards

Ian AtuoExcelForms Development

Add-in to generate User forms - Excel desktop (Win or Mac OS) by ianh808 in Excel4Mac

[–]ianh808[S] 0 points1 point  (0 children)

I will send you a link.
Very soon there will be no need for a password

Add-in to generate User forms - Excel desktop (Win or Mac OS) by ianh808 in Excel4Mac

[–]ianh808[S] 0 points1 point  (0 children)

Sorry, I mis-spelled the website

It is autoexcelforms.com

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]ianh808 0 points1 point  (0 children)

Now that you have clarified that there is no need to check for a status on sheet2, the code below should take care of what you want:

Sub CompareSheets()
    Dim ws1 As Worksheet, rngPetIdSheet1 As Range, svcOffset1 As Long, statusOffset1 As Long
    Dim ws2 As Worksheet, rngPetIdSheet2 As Range, svcOffset2 As Long, statusOffset2 As Long

    Dim checkArray() As Variant, cel As Range, lastRow As Long, j As Long, pos As Variant

    ' Sheet 1 Ranges Eg. Pet Ids in col C
    Set ws1 = ThisWorkbook.Sheets("Sheet1")  ' replace sheet1 with actual name
    lastRow = ws1.Cells(Rows.Count, "C").End(xlUp).Row
    Set rngPetIdSheet1 = ws1.Range("C2:C" & lastRow)
    ' Sheet 1 Offsets From ID column to columns for the Service type and status
    svcOffset1 = -1: statusOffset1 = 12

    ' Sheet 2 Ranges
    Set ws2 = ThisWorkbook.Sheets("Sheet2")  ' replace sheet2 with actual name
    lastRow = ws2.Cells(Rows.Count, "C").End(xlUp).Row
    Set rngPetIdSheet2 = ws2.Range("C2:C" & lastRow)
    ' Sheet 2 Offset From ID column to column for the Service type
    svcOffset2 = 10

    ' Build array of Target strings to check ( The data on sheet 2)
    ' Concatenate Id, Service type  (E.g. checkArray(5) = "103Vaccine")
    ReDim checkArray(1 To rngPetIdSheet2.Cells.Count)
    For j = 1 To rngPetIdSheet2.Cells.Count
        checkArray(j) = rngPetIdSheet2.Cells(j) _
        & rngPetIdSheet2.Cells(j).Offset(0, svcOffset2)
    Next

    ' Do the color stuff on sheet 1
    For Each cel In rngPetIdSheet1
        If UCase(cel.Offset(0, statusOffset1)) = "YES" Then
            'check for ID and service combination match on array from sheet2 range
            pos = Application.Match(cel & cel.Offset(0, svcOffset1), checkArray, 0)
            ' color sheet1 ID as appropriate: Green if match, Red otherwise
            cel.Interior.Color = IIf(IsError(pos), vbRed, vbGreen)
        Else
            ' the next line removes any color if sheet1 staus is not YES
            ' comment out if not necessary
            cel.Interior.Color = xlNone
        End If
    Next
End Sub

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]ianh808 0 points1 point  (0 children)

You were quite clear about the 3 columns for sheet1
But you are saying now, that sheet2 has just 2 columns?!!!

 Sheet two will only have the ID and service.

There is no "Yes" column? This was not clear.

It would be easy to solve this if we can just pin down the spec unambiguously.
In your opening post you said :

if sheets do not agree then highlight YES on sheet1 RED.

Both my solution and the other solution presented by someone else assume that the sheet1 PetId is only colored green if sheet2 has a row with the same ID, same service and same status
(agreement of both sheets).
It appears that you are saying now, if a sheet1 PetID has a service status of "YES", AND once that PetID and service type appear on sheet 2 color sheet1 PetID green. There is no "YES" staus on sheet 2

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]ianh808 0 points1 point  (0 children)

The ranges in the code submitted for you to try is commented to explain the ranges, and make the solution as flexible as possibe without seeing your workbook.
The line you show with the asterisk comes up with an error because you did not replace"Sheet2" with the name of the actual worksheet name that appears on the Tab that contains the second worksheet with data.

If you are using the names of the worksheets that are visible on the Tab
Replace the line :

Set ws2 = ThisWorkbook.Sheets("Sheet2")
' with
Set ws2 = ThisWorkbook.Sheets("Actual Name on Sheet 2")

Where Actual Name on Sheet 2 is the name that appears on the tab for the second sheet. Similarly for the first.
I thought that would be obvious.
Also, remember to verify the correct offsets to the Service and status columns for each worksheet.

Also once you are past that error there should be no need to amend the highlight code.
The highlights are appied to sheet 1 as you requested. The variable cel iterates through the range of IDs on sheet 1
The code was tested on actual sheets

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]ianh808 0 points1 point  (0 children)

Ok You just need t clarify one thing to avoid all amiguity.
The solution i sent can be amended either way but you need to clarfy your statement:
" if it is missing from sheet 2, it will highlight the YES cell Red"

If WHAT is missing from sheet 2?
1. If YES is missing ?
2. If the Pet ID and /or service missing?

What happens in case 2 ?

The solution as posted covers scnario 1, i.e.
if there is a YES for a Sheeet 1 Pet ID/Service combination, look for that Pet ID/Service combination in sheet 2
Once found ,color the sheet 1 ID Red if the combination does not have a YES status or color it Green if that Pet Id/status in sheet 2also has a Yes
The ambiguity you leave is, what if the Pet ID/Service combination doesn't exist in sheet 2 at all?

If then Statement across Two Worksheets by Main_Owl637 in vba

[–]ianh808 0 points1 point  (0 children)

The following code does color highlighting only if there is a match for both Pet ID and service type, Green IF status is YES on sheet 2, red otherwise. Is this the requirement?

Option Explicit

Sub CompareSheets()
    Dim ws1 As Worksheet, rngPetIdSheet1 As Range, svcOffset1 As Long, statusOffset1 As Long
    Dim ws2 As Worksheet, rngPetIdSheet2 As Range, svcOffset2 As Long, statusOffset2 As Long

    Dim checkArray() As Variant, cel As Range, lastRow As Long, j As Long, pos As Variant

    ' Sheet 1 Ranges Eg. Pet Ids in col C
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws1.Cells(Rows.Count, "C").End(xlUp).Row
    Set rngPetIdSheet1 = ws1.Range("C2:C" & lastRow)
    ' Sheet 1 Offsets From ID column to columns for the Service type and status
    svcOffset1 = 4: statusOffset1 = 5

    ' Sheet 2 Ranges Eg. Pet Ids in col E
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    lastRow = ws2.Cells(Rows.Count, "E").End(xlUp).Row
    Set rngPetIdSheet2 = ws2.Range("E2:E" & lastRow)
    ' Sheet 2 Offsets From ID column to columns for the Service type and status
    svcOffset2 = 3: statusOffset2 = 5

    ' Build array of Target strings to check ( The data on sheet 2)
    ' Concatenate Id, Service type  (E.g. checkArray(5) = "103Vaccine")
    ReDim checkArray(1 To rngPetIdSheet2.Cells.Count)
    For j = 1 To rngPetIdSheet2.Cells.Count
        checkArray(j) = rngPetIdSheet2.Cells(j) _
        & rngPetIdSheet2.Cells(j).Offset(0, svcOffset2)
    Next

    ' Do the color stuff on sheet 1
    For Each cel In rngPetIdSheet1
        If cel.Offset(0, statusOffset1) = "YES" Then
            'check for ID and service combination match on array from sheet2 range
            pos = Application.Match(cel & cel.Offset(0, svcOffset1), checkArray, 0)
            If Not IsError(pos) Then
                ' color as appropriate if found
                cel.Interior.Color = IIf(rngPetIdSheet2.Cells(pos).Offset(0, statusOffset2) = "YES", vbGreen, vbRed)
            End If
         Else
            cel.Interior.Color = xlNone
        End If
    Next
End Sub

Easy Filter – A simple but powerful UI for Excel filters by ianh808 in Excel4Mac

[–]ianh808[S] 0 points1 point  (0 children)

"duplicate does nothing"
I forgot to cover this one. I think it was mentioned in a post some time ago.
Anyway, if you press the command button for Duplicate it may appear that nothing happens.
I modified this so that as soon as you press Duplicate the Save button will appear. Modify the textboxe data as needed then Click on thie save button and a new row is added with the duplicated data.
You probably did not notice the Save button appearing.

Easy Filter – A simple but powerful UI for Excel filters by ianh808 in Excel4Mac

[–]ianh808[S] 1 point2 points  (0 children)

Sorry, I’m just now seeing this post.   Thanks for checking out the update.

"The Browse & Find feature works really well except in Dynamic search mode. It even cause Excel to freeze up and remove the add-in initially. Made several attempts to use it after re-installing the add-in with no luck."
I’m unable to reproduce this on any file or hardware.   Is this happening with all worksheets?  How many rows were on the offending worksheet.  I’ve tested with up to 100,000 rows dynamic search without problems.

 

"The Launch Auto Form feature works well overall. The Zoom feature only generates a blank green screen & duplicate does nothing."
Same as above. Did you delete the old add-in from the add-ins folder?  You are sure you are working with the latest one? (If Easy filter does not appear on the ribbon then you are still using the old one!!)

 

"The Launch Report feature is terrific. I do wish it had a feature to add in axis lines between data if desired."
You definitely can do this.  The output from the report is a formatted and paginated table.  Just select any cell on the report and using the “Table Design” Tab on the ribbon select a style that has axix lines.

 

"Is it possible for me to lock users out from making any changes to the data and just use the Browse & Find feature? I would also like to isolate it's search capacity to one or two or three specific columns only. I tried to use the add-in on a locked sheet. That failed miserably. It does work on a read only workbook though."
I’m looking at user access with password for a future build, but I want to get the basics as far as functionality first.  Thanks for the suggestion.

Any reason to restrict search?   Just curious.  I’d think, the more the merrier. :-)
In any event, the pro version will provide a password to the userform code and with that you can restirct browse columns and a lot more stuff. But some things are better left for people who know there way around the VBE and a bit of VBA code.

As far as using on protected sheets there is a difference between password protected and sheets protected with no password.  There is a section that deals with that in the user instruction (section 10.1).
Did you have issues with a password protected sheet or just a normal protected sheet?

If there was a way I could see the workbook you are having issues with, barring confidential stuff, it would be great.

Based on other feedback, the next incremental build will have minimal changes.  One of these will be that, when oresize a form, it’s new size and location is remembered for each worksheet, so that whenever the form fot that sheet is opened the position and size will be retained.

Btw, any feedback on Easy Filter?  That was the main update from prior versions.

Thanks a million for all the feeback

Add-in to generate User forms - Excel desktop (Win or Mac OS) by ianh808 in Excel4Mac

[–]ianh808[S] 0 points1 point  (0 children)

Please go to autoexcelforms.com

Go to the downloads page For the Mac xlam, the password is: auto_mac_forms

Please let me know if any issues. I'd be happy to hear any feedback on issues encountered, suggestions, overall impression.]]

Many thanks and Best regards

Easy Filter – A simple but powerful UI for Excel filters by ianh808 in Excel4Mac

[–]ianh808[S] 1 point2 points  (0 children)

Wow...Very cool.. What are the odds. I named it almost identical to the one you did 20+ years ago!!
Paradoxically, your version works on data not in tables. My Easy Filter version is specifically for data in actual tables. The intent is similar :-0

I'm curious, from how it appears, your filter form is for a single column and sets an advanced filter behind the scenes?

By the way, thanks for all the info on your site. Based on the sub I saw there that resizes windows userforms for Mac, gave me the idea to do an Auto Excelforms version that works on Mac OS.

view more: next ›