Feedback by UserVoice

Col Delane

My feedback

  1. 24 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane supported this idea  · 
  2. 173 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane supported this idea  · 
  3. 726 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for the suggestion Wyn! Thanks also to all the contributors to this thread for the thoughtful discussion and debate.

    Of the many lookup function requests, this is currently one of the highest ranking on UserVoice. Please keep the votes rolling in if you’d like to see this, it helps us prioritize new feature work.

    JoeMcD [MS XL]

    Col Delane supported this idea  · 
    Col Delane commented  · 

    Greg: We'll have to agree to disagree, as I will not hesitate to call out flawed reasoning or specious arguments, even on forums such as this.

    I've seen far too many so-called expert/gurus/bloggers who write about what they perceive as "problems" with VLOOKUP or "disadvantages" when unfairly comparing it to an INDEX/MATCH combination (e.g. can't look left, easily corruptible when inserting columns, etc.) - when their claims just don't stack up. VLOOKUP can look left when nested with CHOOSE, and can handle inserted columns when nested with any one of three functions.

    I don't believe VLOOKUP is difficult to comprehend - after SUM it was one of the first functions I learned to use. If a user can only use single function formulas then they haven't progressed very far.

    Col Delane commented  · 

    "Most users just need an exact version of INDEXMATCH without having the complexity of a formula embedded inside another formula."
    Seriously? How did anyone ever get to be an expert/guru/MVP without nesting functions??

  4. 256 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane supported this idea  · 
  5. 479 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane supported this idea  · 
    Col Delane commented  · 

    I wrote my own macro to Centre Across Selection & Wrap to overcome this deficiency.

  6. 50 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane supported this idea  · 
  7. 843 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for logging this great suggestion, Zack, and to others for voting it up. We’ll prioritize this according to the number of votes, so if there’s more interest, please make sure to register your vote!

    Thanks

    Ashvini Sharma
    Lead Program Manager
    Excel

    Col Delane supported this idea  · 
  8. 986 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for all of the votes – the team has definitely taken notice of the activity around this issue. We moved to SDI as a result of customer requests, but it looks like we’ve got work to do to really nail the use cases people care about. From a read over the comments, I see a number of cases that we will want to investigate further as we think through MDI vs SDI. We’ll get someone from the team to take a deeper look, and we may reach out to some of you for more clarification as we go. Thanks again for all the voting and passion here!

    John, Excel

    Col Delane supported this idea  · 
    Col Delane commented  · 

    A mea culpa!! Please ignore most of my earlier comments about instances in Excel 2016 - it seems that I had a misunderstanding about what a separate instance of Excel looked like in 2016!

    To open a SEPARATE instance in 2016 you can either hold down the Alt key and left click on the Excel icon on your Start page, or using the Windows Run application, type Excel.exe /x (there is a space between the “.exe” and the “/x”), then press return.

    When I do this, the outcome is similar to what used to happen in Excel 2010. That is, a "ring-fenced" application is launched, and a separate copy of my Personal.xlsm file is opened [as Read Only because the original is open in the first instance].

    My apologies if I confused anyone. :-(

    Col Delane commented  · 

    Mats

    I use my PMW as my Excel "toolbox" and library (after all it is just a normal workbook but with a special name). Like your's, mine holds generic tools that I use across any open workbook (no trouble so far), but in it I also store sample code (which I copy to other WBs as req'd), sample sheets, sample formula, as well as examples of Data Validation, Conditional Formatting, Dates, etc.

    A Workbook_Open proc. I have in my (hidden) PMW opens several other workbooks upon startup - and which seems to work just fine, except that only one is added to the PMW instance and the other in its own. However, from what I can tell, there is only one copy of my PMW open across all instances rather than multiples as per your experience.

    Col Delane commented  · 

    Hey FSoriano: I didn't mention it in my earlier comments to this post, but generic macros (tools, etc.) in my personal macro workbook ("Personal.xlsm") work fine on workbooks open any instance, so I'm wondering why you and others have the problem where code in one workbook fails (at least sometimes) when trying to access/manipulate workbooks in different instances. Perhaps this is due to the "special" nature of Personal.xlsm? As a fix, have you investigated whether or not you can insert some code at the beginning of your existing code to make the target workbook 'visible' to the code (e.g. by "Switch Windows" which as I mentioned in my earlier post seemed to change which two workbooks occupied an instance)? Just a thought.

    Col Delane commented  · 

    After further experimentation I've "discovered" the following odd behaviour:
    1. Irrespective of how you open an Excel workbook (e.g. via XL File Open ribbon command or double-clicking the file in Windows Explorer), each will be opened in a separate instance.
    2. In any single instance, Ctrl+Tab will allow you to switch/cycle between the workbook of that instance plus only one other workbook. However, that "one other" workbook can be changed to create a different pairing either by (i) using the Ribbon command View / Switch Windows (where ALL open workbooks are listed) or (ii) selecting the target workbook in the Taskbar across the bottom of the Excel window (where thumbnails of ALL open workbooks are shown)
    3. Curiously, Ctrl+Shift+Tab will allow you to switch/cycle between ALL open workbooks (not just cycle thru open workbooks in reverse order like Excel 2010 & prior allowed!).
    4. Formula links can be created between ANY open workbook but to access all workbooks you must use the Ribbon command View / Switch Windows (where ALL open workbooks are listed) or by selecting the target workbook in the Taskbar across the bottom of the Excel window (where thumbnails of ALL open workbooks are shown) - but even more curiously you CANNOT use Ctrl+Shift+Tab to switch to the desired workbook as described in 3 above!

    This is a clear demonstration of Microsoft's lack of thought, consultation, & testing when modifying the application!

    Col Delane commented  · 

    Microsoft's Excel product developers appear to not understand the axiom "If it ain't broke don't fix it!"
    Overcoming the 2GB RAM limit per instance was a worthy objective, but in doing so they have treated their user customers with contempt and caused significant collateral damage to the ease of use / functionality. Not happy Bill!

  9. 21 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    I understand that Excel 2016 (and perhaps XL 2013) has [partially?] overcome this problem by opening every workbook as a separate instance of Excel whilst still allowing each instance to interact with the others just like having multiple workbooks open in a single instance as in 2010. (Having said that, I do NOT know whether the memory allocated to each instance has been increased in 2016.)

    Col Delane shared this idea  · 
  10. 55 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    Eric & Bob: My apologies, the post I referenced must have been the original code by Faith Granger, which I (& probably with the assistance of others) have since modified or replaced with other code. It's hard to keep track of time and tweeks made to code tools. :-)

    Try this, which also gives the user the option to process only the active worksheet, or all worksheets (excluding Chart sheets which cannot have cell comments).

    Sub Resize_Relocate_CellComments()

    ' Purpose: Adjust size of all cell comment boxes on the active sheet of the active workbook to match the comment text therein, and locate it proximate to the host cell

    'Define Procedure Variables
    Dim wbk As Workbook
    Dim wks As Worksheet
    Dim cmt As Comment
    Dim lArea As Long


    On Error GoTo ErrorHandler

    Set wbk = ActiveWorkbook
    Set wks = ActiveSheet

    Select Case MsgBox("Click:" & vbLf & vbLf & "YES to review & fix comments on ALL sheets in the Active WORKBOOK," & vbLf & vbLf & "NO to review & fix comments on the Active SHEET only, or" & vbLf & vbLf & "CANCEL to abort this process.", vbYesNoCancel Or vbQuestion Or vbDefaultButton2, "Cell Comments Resize and Relocation")

    Case vbYes

    For Each wks In wbk.Worksheets
    Application.StatusBar = "Adjusting comments on " & wks.Name
    For Each cmt In wks.Comments
    With cmt
    .Shape.TextFrame.AutoSize = True
    If .Shape.Width > 300 Then
    lArea = .Shape.Width * .Shape.Height
    .Shape.Width = 200
    .Shape.Height = (lArea / 200) * 1.1
    End If
    .Shape.Top = cmt.Parent.Top + 5
    .Shape.Left = .Parent.Offset(0, 1).Left + 5
    End With
    Next cmt
    Next wks

    Case vbNo

    Application.StatusBar = "Adjusting comments on " & wks.Name
    For Each cmt In wks.Comments
    With cmt
    .Shape.TextFrame.AutoSize = True
    If .Shape.Width > 300 Then
    lArea = .Shape.Width * .Shape.Height
    .Shape.Width = 200
    .Shape.Height = WorksheetFunction.Min((lArea / 200) * 1.1, 100)
    End If
    .Shape.Top = cmt.Parent.Top + 5
    .Shape.Left = .Parent.Offset(0, 1).Left + 5
    End With
    Next cmt

    Case vbCancel
    Exit Sub

    End Select

    Application.StatusBar = False
    MsgBox "All cell comments resized and located proximate to their parent cell.", vbOKOnly, "Cell Comment Autosizing & Relocator"

    ExitPoint: '---------------->>>>>>>>>-------------------->>>>>>>>>-------------------->>>>>>>>> Exit Sub
    On Error GoTo 0
    Exit Sub

    ErrorHandler:
    Resume ExitPoint

    End Sub
    '-----------------------------------------------------------

    Col Delane commented  · 

    Eric Tabor: The VBA by Faith Granger which I referenced in the very first comment to this post both resizes and relocates cell comments.

    Col Delane commented  · 

    This is absolutely necessary if you use Autofilters or Grouping.
    I have adapted VBA code [ posted by Faith Granger on http://www.mrexcel.com/forum/excel-questions/796938-macro-put-all-comments-back-where-they-belong.html ] to auto-size and relocate cell comments on the active sheet or entire workbook (user choice of scope)

  11. 29 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    I too use (occasionally) the N function to add in-formula comments to explain the formula, in preference to a Cell Comment which can make your worksheet look like it's got chickenpox!!

    I very regularly use Alt+Enter to wrap a multi-legged formula over multiple lines to aid visibility/debugging. To make this visibility even better, I utilise some VBA code developed by user PeterG I found in 2015 at http://excelusergroup.org/forums/p/2791/9610.aspx (though this url seems to fail now!) which automatically dynamically sizes (expands and collapses) the formula bar to display all lines when the cell is selected.

    Col Delane supported this idea  · 
  12. 46 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    The issue I raised is NOT about how to deal with duplicate Defined Names in the source and destination workbooks, but about the default (and unchangeable) process that ALL Defined Names from the source workbook, whether used by or relevant to the sheet being copied or not, are copied across to the destination workbook, thus in most cases resulting in redundant external links and a whole lot of unnecessary "noise" in the destination workbook.

    Col Delane shared this idea  · 
  13. 265 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    This is definitely required - and shouldn't need a pile of votes to prompt MS to fix it, for it just makes sense that it should be done!

    Whilst they're at it, they should also fix one of the major causes of this problem, being the replication in the target workbook of EVERY Defined Name in a source workbook when one or more of the latter's sheets (whether or not there are any Sheet-scoped Names, or formulas which include Names elsewhere in the workbook, on the target sheets) are moved or copied from source workbook to target workbook. This could be avoided if during the Move/Copy Sheet process:
    1. Excel identified if there are any Names referencing cells on the target sheets or if there are any formulas on the target sheets that reference Names elsewhere in the workbook, and
    2. users were given the option to replicate all Names, only user-selected Names, or no Names.

    I live in hope but with little expectation.

    Col Delane supported this idea  · 
  14. 18 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane supported this idea  · 
  15. 31 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane supported this idea  · 
  16. 4 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    @ Malcolm: You don't need to enter the formula link and then separately edit out the $. Instead, when you select the target cell/range in the other workbook, just press F4 key to loop through the 4 referencing options until you have the one you want, AND then press Enter to accept the result.

  17. 10 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    The Help documentation for XL2007 & 2010 is crap compared to what was available in XL2003

  18. 71 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for taking the time to post a suggestion Malcolm! We’ll be taking a look at lookup functions. There’s a number of votes on the site for them – I’m not going to merge all of them though because different posts are asking for slightly different things. Please keep voting for the ones you want to see most!

    Cheers,
    John [MS XL]

    Col Delane commented  · 

    You can make a VLOOKUP look left (and act much like an INDEX/MATCH combo by only referencing two single column ranges rather than all in between) by using CHOOSE with an array (but without making it an array formula), as in:

    =VLOOKUP( lookup_value, CHOOSE({1,2}, lookup_column, return_value_range), 2, [range_lookup] )

    Col Delane commented  · 

    There is no need to replace or "fix" VLOOKUP or HLOOKUP - they are not broken! They have a purpose the same as every other function - it's just that many users try to utilise them when another function (or combo) would be more appropriate.

    Most users blindly stick with a hard-coded column offset value rather than return it dynamically by using other functions such as MATCH, COLUMN or COLUMNS (either to lookup a varying column position or to cater for subsequent inserting/deleting of columns in the lookup array which changes the required offset value)

    An INDEX/MATCH combination is no more difficult/unintuitive than the equivalent VLOOKUP, is more flexible/powerful, and it references far less cells if the VL array spans more than 2 columns.

  19. 5 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    Ben: I understand your point about context (I'd use the phrase "logical grouping"), but there's another advantage of having every component book-ended by a space - it allows the user to easily select it for editing or evaluation (by pressing F9 after selecting) without having to be surgically precise with the cursor to ensure parentheses, operators, etc. are not also included.

    More votes would be handy. They should have delayed the ability to vote until all ideas had been locked-in (if necessary by creating rounds/periods for idea submission) so that all voters have the same options on which to vote. Just an[other] idea!

    Col Delane shared this idea  · 
  20. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Col Delane commented  · 

    Thanks for the suggestion Kenneth, but the issue I'm referring to is not about the width of the columns but what rows/columns are included in the dimensions of the structured table. Enter data in the first blank row or column outside a structured table and that row/column will be automatically included in the table - but delete the contents of the last row/s or column/s and the table will not contract to exclude those rows/columns.

    Col Delane shared this idea  · 
← Previous 1

Feedback and Knowledge Base