What I would like to see in Excel 2016

[gkoehler]

I meant be able to use DSUM etc. without having to set names, i.e. use column counter but have that number dynamically update when you add or subtract columns.

 

[Darren Bartrup]

• A one stop 'Where's the last cell' function in VBA or as a formula, rather than the UsedRange with it's problems before saving, or go to the bottom of the sheet and move up, or FIND the last cell.

• Concatenate across a range with optional delimiter.
• Do something with merged cells & center across selection.

 

[Smitty]

I meant be able to use DSUM etc. without having to set names, i.e. use column counter but have that number dynamically update when you add or subtract columns.

Why not use SUMIF instead?

 

[Scott Huish]

I couldn't get that to work, it returned 1:

Excel Workbook
	A	B
1	Don't tell me, but your name is Don, right?	1
Sheet1

@Peter and Robert

I am having the administrators see if the can unbind my sub-forum from their over-riding rule about making people register in order to see my articles. In the meantime, for your "enjoyment", here is the article in question...

InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word<!-- google_ad_section_end -->

The InStr function is a handy tool. It will return the character position of text that is embedded within a larger text string. And while this is handy, the function is useless for finding words as stand-alone words in the text. Let's say you had this text string...

TextString = "Don't tell me, but your name is Don, right?"

and you wanted to find the character position of the name Don in that text. If you used the InStr function like this...

MsgBox InStr(TextString, "Don")

It would return the number 1 because it would find the letters "Don" inside the word "Don't" and never look further into the text to see if Don existed as a stand-alone word or not. Even using InStr's optional arguments and having it perform a binary search would not help because the letter casing for the first 3 characters of "Don't" is the same a for the name "Don". Here is a function which can be called from your <ACRONYM title=vBulletin>VB</ACRONYM> code or which can be used as a UDF on a worksheet, if desired, that will find text as stand-alone words that are not embedded as part of other words...

Function InStrExact(Start As Long, SourceText As String, WordToFind As String, _
                    Optional CaseSensitive As Boolean = False, _
                    Optional AllowAccentedCharacters As Boolean = False) As Long
  Dim x As Long, Str1 As String, Str2 As String, Pattern As String
  Const UpperAccentsOnly As String = "ÇÉÑ"
  Const UpperAndLowerAccents As String = "ÇÉÑçéñ"
  If CaseSensitive Then
    Str1 = SourceText
    Str2 = WordToFind
    Pattern = "[!A-Za-z0-9]"
    If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAndLowerAccents)
  Else
    Str1 = UCase(SourceText)
    Str2 = UCase(WordToFind)
    Pattern = "[!A-Z0-9]"
    If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAccentsOnly)
  End If
  For x = Start To Len(Str1) - Len(Str2) + 1
    If Mid(" " & Str1 & " ", x, Len(Str2) + 2) Like Pattern & Str2 & Pattern Then
      InStrExact = x
      Exit Function
    End If
  Next
End Function

This function takes three required arguments and provides for two additional optional arguments. The first argument, named Start, is required and lets you set the starting character number for the search to begin at (use 1 to start the search at the first character). The second argument, named SourceText, is required and is the text to look for the word in. The third argument, named WordToFind, is required and, as the name indicates, is the word whose position you want to locate. The fourth argument, named CaseSensitive, is optional and allows you to make the search case sensitive or not (the default is FALSE representing a case insensitive search). The fifth argument, named AllowAccentedCharacters, is optional and allows certain accented letters to be considered as being part of a word (the default is False meaning only normal ASCII characters can make up the word). For example, with the default value of False, the word "resumé" would never be found because the accented "e" (é) would be considered a non-letter. Setting the fifth argument to True would allow the function to find the word "resumé". I only allowed for three accented letters "ç", "é" and "ñ" (in both lower and upper case), but you can add more if you wish by modifying the UpperAccentsOnly and UpperAndLowerAccents constants (the Const statements at the beginning of the code).<!-- google_ad_section_end -->

 

[Rick Rothstein]

I couldn't get that to work, it returned 1:

Sheet1

	A	B
Don't tell me, but your name is Don, right?

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =InStrExact(1,A1,"Don") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I could not get it to work either.

See the revised code in my mini-blog article here...

InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word

 

[dannyfry]

A more user friendly HLOOKUP and VLOOKUP because they always trip me up

 

[Scott Huish]

XLOOKUP to replace INDEX MATCH MATCH

 

[Smitty]

XLOOKUP to replace INDEX MATCH MATCH

But INDEX/MATCH/MATCH can make you look so cool!

 

[Scott Huish]

Native Regular Expressions functions.

 

[Fazza]

I'd like to see

• conditional formatting revert back to the basic three options (such as in Excel 2003)
• pivot tables revert back to Excel 2003
• simpler data connections (so more users can do this easier,though it is OK for me as is) for queries & pivot tables
• simpler queries and database-like functionality - so users wouldn't need formulas for things like INDEX/MATCH or multiple lookups, and also users could avoid array formulas. Array formulas IMO are the biggest mess in Excel ATM
• database like reporting. So from a query or table in Excel one could go straight to an Access type report. This did exist ~20 years ago but was removed
• menu structure revert to Excel 2003 style
• print preview revert to Excel 2003
• removal of merged cells functionality!
• data forms like Access forms with their simple, powerful & handy built-in filtering, sorting, etc

Or from another perspective, revert Excel back to Excel 2003 specifications & leave it there (including lower row & column limits), develop Access (wizards or whatever it takes) so that it is easier for spreadsheet users to transition to databases.