Microsoft Excel

131K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
0
 
LVL 4

Author Comment

by:Bob Flisser
Comment Utility
Thanks, Andrew!
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
0
 
LVL 49

Expert Comment

by:Martin Liss
Comment Utility
Thanks, that helps and I suggest you mention that the lists are related and also include the description of what you mean by "true database form". And rather than
Each option also then has an identifying code allocation. The codes are calculated in columns H to M...
I'd suggest something like
Each option also has an identifier which is generated via the formulas [that's more familiar to American audiences than formulae] found in columns H to M...
You should also change the reference to "code allocation" in you next to last paragraph.

I find
However, if these three lists are used for DV they would not be dynamic and change based on the selection from the previous category.
a little confusing. Do you mean
However, if these three were used for DV as is, they would not be dynamic because [explain why]

I'm going to publish this no matter what but consider my suggestions and let me know when you're done.
0
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
2
 
LVL 26

Expert Comment

by:Shums
Comment Utility
Great Expert with great mind
0
 
LVL 49

Author Comment

by:Martin Liss
Comment Utility
Thanks.
0
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
2
Warning
This article describes a serious pitfall that can happen when deleting shapes using VBA.
3
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
0
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will discuss how to use VBA to automate Excel.
2
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are given for Office versions from 2003 through 2016.
0
 
LVL 31

Author Comment

by:Helen Feddema
Comment Utility
I will see if I can get that information.
1
 
LVL 31

Author Comment

by:Helen Feddema
Comment Utility
David -- this is most curious.  For Access 2010, the location for database templates is C:\Program Files\Microsoft Office\Templates\1033\Access.  But for Access 2016, it is C:\Program Files (x86)\Microsoft Office\root\Templates\1033\Access\Part.  I have 64-bit Office and Windows, in case that makes a difference (it probably does).
1
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
2
 

Expert Comment

by:MrJaxn
Comment Utility
Thanks Stacy!! Worked for me perfectly! I installed a few of the Office 2016 64-bit and 6 months later, "my Spreadsheet Compare doesn't open up!"
One trip to Experts Exchange and I have a fix courtesy of Stacy.
1
Using the Hyperlink formula in Excel
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
3
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Decision Making
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
27
 

Expert Comment

by:Brendan Wilson
Comment Utility
This is an excellent article. I appreciate you taking the time to write this.
1
 

Expert Comment

by:Judy Deo
Comment Utility
Thanks for taking the time to post this in such detail. Appreciate it.
1
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to do.
27
 

Expert Comment

by:Brendan Wilson
Comment Utility
I read the other article that you wrote, and they both have been very useful. Thank you for taking the time to put this information together.
2
 

Expert Comment

by:Judy Deo
Comment Utility
Wow this was so simple and I did not know it. Nice to know now and I know for sure I will have many opportunities to use this in the future.
1
Security
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
2
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
2
 
LVL 33

Author Comment

by:Rob Henson
Comment Utility
Hi Thomas,

Thanks for the pointers. I have now edited the article as suggested and am happy for it to proceed.

Thanks
Rob H
0
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
1
Screenshot of Automated Link Checker
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink.
1
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
0
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for you to try out.
2
In this article we have discussed about the OS X EI Capitan and how to fix Wi-Fi issue in OS X El Capitan. We have explained how to delete system level preferences and create a new Wi-Fi location to resolve Wi-Fi issue.
1
Free Tool: Path Explorer
LVL 9
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Eggs in one array
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes a way to elegantly solve the problem.
6
 
LVL 1

Expert Comment

by:sdeller
Comment Utility
The suggestion using UBound is incorrect.  With the code like this:
  CustData(UBound(CustData)).strCustName = ...
  CustData(UBound(CustData)).dblSales = ...
the code will create a new entry with the first assignment, causing UBound to increment. So the second line will refer to yet another new entry.  You need to assign both values with one assignment.
0
 
LVL 49

Author Comment

by:Martin Liss
Comment Utility
I'm sorry but that is not correct. In VB6, once an array is created the only ways to increase the upper bound of the array is through Dim, Redim and Redim Preserve. If you place this bit of code in a new project you'll see that I'm correct.

Option Explicit

Private Type CustomerData
    strCustName As String
    dblSales As Double
End Type
Private Sub Form_Load()
Dim CustData() As CustomerData
Dim lngIndex As Long

ReDim CustData(2)

CustData(0).dblSales = 0
CustData(0).strCustName = "Customer 0"

CustData(1).dblSales = 100
CustData(1).strCustName = "Customer 1"

MsgBox "The upper bound of CustData is " & UBound(CustData)

CustData(UBound(CustData)).dblSales = 200
CustData(UBound(CustData)).strCustName = "Customer 2"

MsgBox "The upper bound of CustData is still " & UBound(CustData)

For lngIndex = 0 To UBound(CustData)
    Debug.Print CustData(lngIndex).strCustName & " has sales of " & CustData(lngIndex).dblSales
Next
End Sub

Open in new window

0
Oh no
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
68
 

Expert Comment

by:M Melson
Comment Utility
Very nice article. Thank you for taking the time writing and sharing it.  I can use part with variable names to indicate the type of variable. Good advice.
0
 
LVL 49

Author Comment

by:Martin Liss
Comment Utility
Thanks.
0
Example PowerPoint Add-In
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
5
 
LVL 17

Expert Comment

by:Kyle Santos
Comment Utility
Great job!
0
 
LVL 12

Author Comment

by:Jamie Garroch
Comment Utility
Thanks Kyle Santos :-)
0
Where used to see Gantt charts for illustrating project timelines, but what if I wanted to visualize passed timed events? Here's how.
5
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nice job illustrating.  Voted Yes.
0
 

Expert Comment

by:kev_hinds
Comment Utility
Pretty decent.  I am sure it could come in handy some day.  Thanks!
0
Create your own formula
This article describes how to create your own Excel formula when there isn't a built-in formula that meets your needs.
19
 

Expert Comment

by:J. Andrew Smith
Comment Utility
This is for Sach44: a co-worker of mine came up with a very nice way to suppress automatic recalculation and then restore it to its previous state -- after all you might have two procedures, A calling B, and if you blindly set it to manual at the top of each, and automatic at the bottom of each, then it will be automatic right after A has called B.  But, if you set up a local variable in each function to hold its state, you have to remember to include a statement at the bottom to restore it, so now you can't use Exit Sub/Function, and you even have to write up an error-handler to resume at the statement that restores it -- what a pain!

Here's the nice alternative: a class that suppresses auto-calculation when instantiated and restores the state when destroyed:

Class clsCalcSuppressor
Option Explicit

Private FormerStatus As XlCalculation

Private Sub Class_Initialize()

    FormerStatus = xlCalculationAutomatic       ' Establish default

    If Workbooks.Count > 0 Then                 ' With no workbooks open, just mentioning "Application.Calculation" causes type mismatch!
        FormerStatus = Application.Calculation
        Application.Calculation = xlCalculationManual
    End If

End Sub

Private Sub Class_Terminate()
    If Workbooks.Count > 0 Then Application.Calculation = FormerStatus
End Sub

Open in new window

Now all you have to do is include two statements just at the top of any procedure:
Dim CS As clsCalculationSuppressor
Set CS = New clsCalculationSuppressor

Open in new window

...and when the procedure ends by any means, the state is restored, because CS is local and therefore gets destroyed.
(But what I wonder is: why doesn't the one statement Dim CS As New clsCalculationSuppressor work?)

Of course this technique can be used for many other application properties, e.g., the status bar, displaying alerts, enabling events, the mouse cursor, screen updating...
0
 
LVL 1

Expert Comment

by:Sach44
Comment Utility
Hi Andrew.

On my part, I have carefully stored the many original Excel user settings using registry entries upon activation (commandar("ply"), decimal seperator, thousand seperator, xlcalculation, ...), modified them for my needs, and then set them back to the original state on the workbook deactivate_event (that is launched on close and opening a workbook in the same excel instance). Curiously, I have noticed that the orginal usersettings remain if if a new Excel instance is created with my workbook previously openned in its own instance. This appears to work alright for my needs at the moment. Tested using Excel 2013 64 bit and Excel 2010 32bit.

As I am a beginner, I suppose the solution you suggested is quicker; I will give it a try once I get a chance. Thanks!
0
This collection of functions covers all the normal rounding methods of just about any numeric value.
2
 
LVL 51

Author Comment

by:Gustav Brock
Comment Utility
Finally I have these assembled. Through the years bits and pieces were collected, but now I had the option for a rewrite.

/gustav

PS: There are minor flaws in the layout but the editor is somewhat strange so I couldn't get it completely straight.
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nicely laid out.  Voted Yes.
0

Microsoft Excel

131K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.