Posts by mdenino

    You spoke of having your data in two different sheets, but provided two different workbooks. Which way would you like this to be addressed?


    If your needs allow you to combine the two workbooks into one, you can use conditional formatting to highlight matches.

    1. Move or Copy sheet from 2nd workbook into 1st workbook
    2. Select all three data columns in the first sheet
    3. Create a conditional formatting rule using the following formula:

    =NOT(ISERROR(MATCH(A1,'[Name of 2nd sheet]'!A:A,0)))


    Replace [Name of 2nd sheet] with the actual name of the 2nd sheet. Be sure to include the 'single quotes' around the sheet name if it includes any spaces. (It's not a bad idea to include them either way.)


    If you'd like still like VBA solution too, please clarify whether combining your two data sets into one workbook is acceptable.


    Cheers

    Disclaimer: For reasons outside the scope of this inquiry, I am trying to learn the reason my workbook is behaving the way it is, NOT looking for suggestions about how I could restructure my workbook to "work around" or "avoid" the issue. Any/all help would be most welcome!


    Details:


    I have a workbook which has been begun performing poorly for me, and which I have stripped down little by little in order to uncover the root cause(s). It is currently saved as .xlsx, with 10 sheets in it. All sheets use defined names, all of whose scope is the entire workbook.


    Four of the sheets take about 20 seconds to activate or to execute a change in a cell. The others behave quickly and normally.

    • When I delete the named ranges of the slow sheets, they behave normally.
    • When I recreate the named ranges from scratch, the sheets become slow again.
    • The named ranges of the sheets that take a long time to activate are not used in formulas elsewhere in the workbook.
    • The slow sheets have 8, 11, 18, and 22 named ranges, respectively.
    • The sheet with the most named ranges by far is not one of the slow sheets. It has 54 named ranges.


    All sheets are laid out as follows:

    • Defined names begin with "[abbrev]_", where [abbrev] is a one- or two-letter shorthand form of the sheet name
    • Defined names encompass entire rows or columns
    • A defined name called "[abbrev]_HEADERS" covers row 2
    • A defined name called "[abbrev]_CATEGORIES" covers row 1
    • Columns containing a header in row 2 have a defined name, "[abbrev]_[headername]"


    Some other steps taken:

    • Ensured the troublesome file is saved on the local HD as opposed to a remote server
    • Updated Office
    • Rebooted
    • Run the document inspector and followed its recommendations
    • Tried two different licenses:
      • MS Office 365
      • MS Office 365 ProPlus


    I have had no luck whatsoever resolving this issue. What could be causing this problem?


    Thanks in advance!