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
Get HTML5 Certified
LVL 9
Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

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 48

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 25

Expert Comment

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

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).
0
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
2
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
Online Training Solution
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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.
25
 

Expert Comment

by:Aamir Hussain
Comment Utility
A simple and excellent solution of the complicated question. Well done Mr.Shums :-)
1
 

Expert Comment

by:Brendan Wilson
Comment Utility
This is an excellent article. I appreciate you taking the time to write this.
0
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.
25
 

Expert Comment

by:Aamir Hussain
Comment Utility
A very good cooperative and professional expert.Always give new idea with excellent logic .
1
 

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.
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 48

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 48

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 16

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.