Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Can the "Date Picker" from Infopath be used in Excel?

I am trrying to insert a date picker in an excel file, similar that which is
used in Infopath. Is this a possibility of impossible?


Post your answer or comment

comments powered by Disqus
The number of items that can be undone using 'undo buttons' appears to be
sixteen in Excel 2002. Can this number be increased?

I am sure in previous versions of Excel the number of undoes was much
higher, one hundred springs to mind.

can auto text be used in excell like it can in word?

I created several columns (fields) in my excel table, one of the column is
named as "SIZE".

Could you please advice if "SIZE" is one of the reserved words restricted to
be used in Excel especially when creating a new column name?

Can I have a complete list of reserved words that are restricted in excel
column fields?

Thanks

I have a an MS Query with a table 'tblReports' this displays a list of orders and action taken in the 'Details' column. I have worked out that by using 'Is Null' in the value field I can find all the entries which have no action taken against them.

However, I want to find all the entries actioned by 'LIZ'. Unfortunately the field shows: LIZ:08/07/2008:15:32:43.

Can someone help me with the syntax I should be using in the 'Value Field' to show all the entries that correspond to LIZ?

Grateful for any help you can offer.

Can the system variables like %HOMEPATH% be used inside of Excel? If so then
how?

can hlookup formula and vlookup formula be used in combination

Hi,
I really need help on this problem which took me ages to solve!
I am working on a nxn matrix and I have named the following ranges.

	VB:
	
 n-1 
    range(.Cells(i, i + 1), .Cells(i, nrandpoints)).Name = "Change" & i 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This means that if n=30, for example, there will be Change1 to Change29. These values are to be used in Excel Solver as the 'ByChange' values. Initially, I wrote a code like this:

	VB:
	
 Change29), range("MinX")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works fine for 'n' up to 30 but then I started to get error message 'wrong number of arguments or invalid property assignment' for n=31 onwards. I will need to run the Solver for up to n=50. I tried to use For Loop like below but still didnt work:


	VB:
	
 nrandpoints - 1 
    SolverOk SetCell:="MinX", MaxMinVal:=2, ByChange:=Union(range("Change" & i), range("MinX")) 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please advise me on this matter as I really really need this done..
Many thanks!

I would like to produce charts/graphs in excel using our custom corporate
colours for which a have the various values. Is this possible to set up?
And if so how?
Thanks


1. Drag the Date field to Row Labels area in PivotTable Field List dialog box (this area must contain a Date field only).
2. Select a cell in the Date field, right-click and select Group.
3. In the Grouping dialog box, select Days, and in Number of days set the desired Number of days.

Assuming you want to Group the dates by N number of days when the first work day is Monday.
1. Enter the first date in Date column in the source data, select the cell, and then press Ctrl+1 (to open Format Cells dialog box) -> Number tab -> Custom -> In Type box , type DDDD, to find which day of the week is the date that appears in the cell, then find the date of the first Monday before that date.
2. Select the PivotTable sheet and select a cell in the Date field, right-click and select Group.
3. In the Grouping dialog box, type the date you found in step 1 , then select Days, and in Number of days box set the desired days number.

You can Group a Date field only once, either by Number of Days or by Days, Months, Quarters, and Years, as explained later.
If you want to Group the Date field by both Number of Days or by Days, Months, Quarters, and Years:
1. Copy and insert the Date column in the source data so that there are two Date columns.
2. Change the heading title of the new Date column.
3. Add the new field to the PivotTable report, and then Group each one of the Date fields separately, one by Number of Days and the other by Days, Months, Quarters, and Years.

how do i change the column headings from numbers to letters in excel?

Is there a way to pull just the last word from a text string in Excel?

I have a sheet with a column of text descriptions that are various lengths
and the last words in each cell are various lengths. Basically I want
everything to the right of the last space.

Thanks!

I have a Userform that I created in Excel 2007 with a Calendar. When I open this in Excel 2010, the Calendar isn't part of that version, so the object is removed from the Userform and I am forced to comment out the calendar script.

I have heard about the Date Picker, but I have no idea how to use it.

Is there a tool I can use to allow the Calendar to come up when the Userform is opened with Excel 2007 and the Date Picker when the program is opened using Excel 2010?

This calendar allows the user to select a date and then the information is passed to a cell where the date is filled in.

I need the script to be versatile between the two and I'm racking my brain trying to figure this out.

Any advice or direction is greatly appreciated. I have no idea where to start when trying to combine the two date options.

Here is what I've done so far on my own... My mind is very scrambled... Please help!

Private Sub cboEnrollmentAdvisor_Change()

End Sub

Private Sub cmdClearForm_Click()
    Call UserForm_Initialize
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOK_Click()
Dim NR As Long

'transfer values to database
    With ActiveWorkbook.Sheets("November2011")
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("B" & NR).Value = txtFName.Value
        .Range("C" & NR).Value = txtLName.Value
        .Range("D" & NR).Value = txtIRN.Value
        .Range("A" & NR).Value = cboEnrollmentAdvisor.Value
        .Range("E" & NR).Value = frmCalendar.Value
    End With

'Reset values
    txtFName.Value = ""
    txtLName.Value = ""
    txtIRN.Value = ""
    cboEnrollmentAdvisor.Value = ""
    frmCalendar.Value = ""
End Sub

Private Sub cmdSubmit_Click()

End Sub



Private Sub TextBox1_Change()

End Sub

Private Sub CommandButton1_Click()
    Unload Me
    ActiveCell.Select
End Sub
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

    ActiveCell = MonthView1.Value
    Unload Me
    ActiveCell.Select

End Sub
Private Sub UserForm_Activate()
    If Not IsDate(ActiveCell.Value) Then
        Me.MonthView1.Value = Date
    Else
        Me.MonthView1.Value = ActiveCell.Value
    End If
End Sub
Private Sub UserForm_Initialize()
    Dim frm As Long, frmstyle As Long
    If Val(Application.Version) >= 9 Then
        wHandle = FindWindow("ThunderDFrame", Me.Caption)
    Else
        wHandle = FindWindow("ThunderXFrame", Me.Caption)
    End If
    If wHandle = 0 Then Exit Sub
    frm = GetWindowLong(wHandle, GWL_STYLE)
    frm = frm Or &HC00000
    SetWindowLong wHandle, -16, frmstyle
    DrawMenuBar wHandle

    With Me
     .Left = ActiveCell.Offset(0, 1).Left
     .Top = ActiveCell.Top + ActiveCell.Height + 15
     .StartUpPosition = 0
    End With

End Sub



Private Sub Image1_Click()

End Sub

Private Sub Label9_Click()

End Sub

Private Sub Degree_Initialize()
    txtFName.Value = ""
    txtLName.Value = ""
    txtIRN.Value = ""
    With cboEnrollmentAdvisor
        .AddItem "********************************************Undergraduate
Degrees*******************************************"
        .AddItem ""
        .AddItem "Bachelor of Science in Early Childhood Education (Leads to Credential)"
        .AddItem "Bachelor of Science in Elementary Education and Special Education (Dual Major) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in Early Childhood Education)
(Eligible for Institutional Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in English) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in Math) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in Science) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in Business Education) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in English) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in Math) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in Social Studies) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education with an Emphasis in Biology (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education with an Emphasis in Chemistry (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education with an Emphasis in Physical Education (Eligible for
Institutional Recommendation)"
        .AddItem ""
        .AddItem ""
        .AddItem "********************************************Graduate
Degrees*******************************************"
        .AddItem ""
        .AddItem "Master of Arts in Teaching with an Emphasis in Professional Learning Communities (Not Eligible for
Institutional Recommendation)"
        .AddItem "Master of Arts in Teaching with an Emphasis in Teacher Leadership (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Curriculum and Instruction: Reading with an Emphasis in Elementary Education
(Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Curriculum and Instruction: Reading with an Emphasis in Secondary Education
(Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Curriculum and Instruction: Technology (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Early Childhood Education (Leads to Credential)"
        .AddItem "Master of Education in Early Childhood Education (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Educational Administration (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Educational Leadership (Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Elementary Education (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Elementary Education (Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Elementary Education: Arizona Teaching Intern Certificate Program (Eligible for
Institutional Recommendation)"
        .AddItem "Master of Education in Secondary Education (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Secondary Education: Arizona Teaching Intern Certification Program (Eligible
for Institutional Recommendation)"
        .AddItem "Master of Education in Special Education for Certified Special Educators (Not Eligible for
Institutional Recommendation)"
        .AddItem "Master of Education in Special Education: Cross-Categorical (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Special Education: Cross-Categorical: Arizona Teaching Intern Certification
Program (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Special Education: Cross-Categorical (Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Teaching English to Speakers of Other Languages (TESOL) (Not Eligible for
Institutional Recommendation)"
        
    End With
    cboEnrollmentAdvisor.Value = ""
End Sub

Private Sub CommandButton1_Click()

End Sub


I have a workbook with several worksheets, each set up as a database.

I would like to utilize a named formula that will return the range that matches from the first day of the month to the actual date in column A.

Using several cells to creat formulas for returning the first day of the month for the current row, matching that row in the 'Bulk Data' worksheet, etc etc to finally came up with a formula that would return the text for the range.

I used this formula in a test cell (K4) and it returns the proper range to the cell.
The value returned is 
In another test cell (L4) I entered the following formula
and it does return the sum of the cells in the range referenced in cell L4.

I know this is a very long way to get to the sum, but I need the formula to be dynamic and was hoping I could turn it in to a Named Formula.

The problem is that at any step along this development I can turn the formula into a Named Formula and all works until I use the function INDIRECT. The I get the #VALUE! error.

Can INDIRECT be used in a Named Formula or do I need to turm this into a User Defined Function through VBA?

Or is there a more simple approach yet?

HI folks

I am using the following formula on an MI Sheet to calculated the income on loans of various types taken from the Loans Sheet, which works well calculating interest and feesfor different classes of loan that are completed.

=SUMPRODUCT(--(Loans!G$3:G103="Homeloan"),--(Loans!D$3:D103="Completed"),Loans!M$3:M103)

=SUMPRODUCT(--(Loans!G$3:G103="Car loan"),--(Loans!D$3:D103="Completed"),Loans!M$3:M103)

However I would like to refine the result by working out how much interest is paid during a calendar year, so if the loan completes in January it will show more interest paid than if it completed at the end of the year.

If the date of completion cannot be used I wonder if CHOOSE can help if I then just used the month of completion to give a rough answer

Can the contents of a Cell be copied to another Cell by simple Hovering?

Dear Forum,

I am no VBA Expert but follow the logics very well if a File's attached..with some comments.

Now, the requirement is that I have sesveral HEADERS that is going to be horizontally placed from Columns A till Q.. Lets say with the name of Different Processes.

And I have the Database below this Headers form Row 3 till Row 103..

Now lets say these Headers containing the Different Processes are present from A1 till Q1 and I want to simply Hover over any of these headers and want the contents copied in cells B3 onwards till B103.

Now i know that we can use a Dropdown, howeer this has to be done fast and if this is possible via VBA I will happy to use this feature,,

The way it would work is if I am hovering my Mouse over Cell A1 which has the Process of "Research" then this text should get copied to the Cell B3 which is already SELECTED prior to hovering...
The values should be copied to only clolumn 2 i.e B3 till B103..

I am not sure but hope this is possible through VBA..

Warm Regards
e4excel

Hi guys,

How/where do I create a macro that can be used in any open workbook. I want to be able to run the macro on any workbook that is opened.

Thanks,
Julie

Hi.

I have an expression in my main Management accounts workbook.

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMIF(FigaroTBMaster.xls!FigeroYTDNominal,"= "&$A34,
FigaroTBMaster.xls!FigeroYTD04),0)

Were "CurrentPeriodNumber" =4,
AE$451 =4,
$A34 ='99999
"FigaroTBMaster.xls" is the workbook containing the source data,
"FigeroYTDNominal" and "FigeroYTD04" are named ranges within the source file.

The expression works when the source file is open, but not when it is closed.

Can named ranges be used in formulae which link workbooks?

I want to get the range from a different sheet to be used in the Indirect function.

For example:
I have 11 sheets, DATA and VIEW 1..,VIEW 2,........, VIEW 10.
I have ten parameters in DATA and Im making 10 sheets for ten parameters..

Each of these VIEW sheets would be same except for the last column (i.e. G )

For example the first parameter in the DATA sheet is Population which would be placed in column G in VIEW 1 and also the next parameter Income in column G in VIEW 2..
However the 1st parameter would be in column g in DATA but the 2nd parameter in column H, 3rd in column I, so on so forth..

I want a formula which gives me RAnge to look out for in DATA like

$G$3:$G$200 , considering that the first row of retrievable data starts from the third row and the first column A but there would be a issue for 9 parametes except the 1st as all ofthe are not in column g..

Any ideas...!

Hi. I am trying to store a variable (I will highlight it in red below) to be used later in the same formula. The formula I have now works, but it puts the variable in cell G20, and I do not need this variable anywhere on the sheet. However, I do need it to perform some calculations later in the code. Here is the code:

Sub y()

    Dim c As Range, lrow As Long
    
    lrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    For Each c In Sheet1.Range("A2:A29" & lrow)
        If c.Value = "Mike" And c.Offset(0, 1).Value = "Fax" Then
            Range("G20").Value = Range("G20") + c.Offset(0, 3).Value
        End If
    Next c
    
    Range("G21").Formula = "=$G$20 /
SUMPRODUCT(--(A2:A29=""Mike""),--(B2:B29=""Fax""))"
    
    Application.ScreenUpdating = True

End Sub

As you can see, in order to get G21, I am using the value from G20. Ideally, I want to be able to calculate G21 without G20 being visible. So instead of my code saving this number in G20, is there a way for the code to remember or store the value (of G20) to be used in the following formula (bottom of top code):



Can an IF statement be used in a Printing Macro for a multi-tabbed workbook? The workbook has around 15 tabs that at times do not have data on every tab. What is needed/wanted is a printing Macro that will let you print the workbook out, but that will only print out the tabs that actually have data entered on the individual worksheets and not print the ones that dont have data.

Hi All,

Potentially a tricky one (not least because I might be going about
doing it in a bit of a backwards way)...

I've got a large amount of data in a MS Project file, which is too much
to be able to be able to use as a summary document. To this end, I've
created a series of macros which use the project data to prodcue
summary charts and graphs as required - this is done and dusted with...

The bit that remains, and that I'm struggling with, is how to simplify
the process. I want a single button, in excel, that when pushed does
the following:

1. Opens up hte relevant Project file
2. extracts the required fields (for example, name and start date) from
that project file and saves them elsewhere in the workbook.
3. closes back up the project file - and lets my other macros do their
magic on the resultant data...

To be honest, I can' t do any of these - but I'm particularly stumped
by 2. I've thought about trying to save the data in project as an .xls
(using a template as required), or getting excel to run some macro in
Project which could do the extract.... Any help with these or examples
of bits of code that might help would be fabulous!

Thanks in advance....

Chris

Hi.

I have an expression in my main Management accounts workbook.

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMIF(FigaroTBMaster.xls!FigeroYTDNominal,"="&$A34,
FigaroTBMaster.xls!FigeroYTD04),0)

Were "CurrentPeriodNumber" =4,
AE$451 =4,
$A34 ='99999
"FigaroTBMaster.xls" is the workbook containing the source data,
"FigeroYTDNominal" and "FigeroYTD04" are named ranges within the source file.

The expression works when the source file is open, but not when it is closed.

Can named ranges be used in formulae which link workbooks?

print area can't be defined in Excel?

One of our users showed me this yesterday:
http://marcmedina.com/Microsoft/excel_issue.jpg

In 2003 she was able to define the columns of a spreadsheet to print, but it's not able to be chosen in 2007. She has a full install and I went through the security settings (and the other options too) but can't find anything to resolve it.Has anyone else run into this?

To be more specific, she is trying to do this from the print preview menu. I did find how to do it within Excel, but this is a user who finds it very difficult to change "the way she's always done it".
I just spoke with her again and got more clarification. This particular spreadsheet is 5 pages wide. She needs to print it so that column A is at the beginning of each page as it prints.

Hi everybody,

I was wondering if any of you knew if this formula can be used in 3d

I wanted to include my OCT worksheet to catch extra values.

=SUM(IF((NOV!$M$4:$M$337=C$1)*(NOV!$A$4:$A$337=$B2),'[NOV!$L$4:$L$337))

Thanks for any help in advance =-)

Adam


No luck finding an answer? You could always try Google.