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

Free Microsoft Excel 2013 Quick Reference

Formula to show aging in ranges Results

I have an accounts receivable report and would like to insert a column that would show the age of the items in 30 day increments, up to 180 days. The date of the receivable is in column G and the date I am comparing it to is in cell R1. I would like to show current if it is up to 30 days, then 31-60 would show as 60 days, 61-90 as 90 days, 91-120 as 120 days, 121-150 as 150 days, and 151 days + as 190 days.

Thanks for your help.

Hi all,

I'm trying to chart some data from workings I have generated. I wish to keep the same chart (and data source range) should I change certain assumptions of the variables for ease of reference.

Essentially I wish to show the total value of investments for a person for the period up to when they hit 65 (retirement age). I do not wish to show any data beyond the age of 65.

Is there a way that I can limit the chart to display data to reflect this? e.g. if someone is 55 now I wish to only show 10 years of projections and the chart to cut-off at 65, but if I change the date of birth to 1982 the chart would display 35 years of projections?

The attached shows roughly what my problem is. At present the chart is reflecting the blank cells as a value of 0, whereas I don't wish to see it in the chart if the value is blank.
Any help greatly appreciated.

P.s. I have included a formula in each of the cells to return a blank cell if the age goes over 65. The chart is based off data from cells A5:C52.

hi, thanks for looking

I have succesfully created dynamic ranges which I use with a chart, using the formula =OFFSET($G$1,0,0,COUNTA($G$1:$G$50),1) I have adapted the formula for each series, but for the sake of this question its just to show the formula I'm using.

The problem I have is that one of the data ranges relates to someones age. I want to limit the chart to an age range 50 -100.

Its not as straight foward enough to assume the age 100 occurs in the same cell, because the start of the range is the current age, and of course for someone age 80 the range will be quite small, and for someone age 55 its going to be longer.

Using a match formula I can locate the row that age 100 occurs how can I adapt my =offset formula to say only expand to age 100?

Any help appreciated
Chris

My Excell worksheet has data in 6 columns (A, B, C, D, E, F) and 20 rows.

Header row contains Name, City, Age, Young, Male, Female

Columns D, E, & F contain numeric data only consisting 1 or 0 (to show YES or NO)

Now, I want to make summary of this database based upon the following conditions (for example):

Count number of rows if:
--- column A (range) = "Name" AND (column D (range) = 1 OR column F (range) = 1)

(I want to know how many persons of same name have "1" in any of D, E columns i.e. number persons of same name who are either young or male or both )

--- Also number of persons of same name who are "young" but NOT male

I want to use SUMPRODUCT but do not know how to use it to get result similar to AND, OR, NOT.

Thanks for your help.
Ramazan

I am trying to count occurances in an Excel 2003 spreadsheet that meet three different criteria. The criteria are:

(1) the type of document is "Procedure - LOB" (this is a text field)
(2) the age of the document does not equal "TBD" (this is a column of numbers or the text word TBD.
(3) the age of the document (# days before/after deadline)

Document Description
Document TypeDays
Past
Deadlinedocument 1Policy - LOB7document 2Procedure - LOB0document 3Policy - CRM-34document 4Policy - Fund-111document 5Procedure - LOB-8

The following formula works perfectly to capture overdue documents (those with an age greater than 0).

=SUM(($C$2:$C$376="Procedure - LOB")*($I$2:$I$376>0)*($I$2:$I$376"TBD"))

My problem is that I also need to count the same data (1) and (2), but where the age condition changes to a range of numbers. I need to capture incidences where the "age" is greater than -60 (and) less than or equal to 0. The goal is to show documents within 60 days of the due date.

I can't figure out how to change the middle section of my original formula to capture a range of numbers versus a single number.

Any ideas?

I’m trying to use this formula.
=IF(C25>0,C25/100,IF(C24>0,C24/100,IF(C23>0,C23/100)))

I have the following data set:
B20:B25: shows ages in 5 year increments (i.e. 40, 45, 50, 55, 60, & 65)

C20:C25: self populates with Future Value numbers based on the age in cells B20:B25 and some other cells.

D27: divides the value in C25 by 100.

My problem is that C25 is not always a number. Depending on the age range in B20:B25, the output for C25 may be #N/A. In that case, I want the formula to look at C24 and divide that number by 100. If C24 is also #N/A, then I want the formula to look at C23. Does that make sense?

Thanks for reading this post.

I have a range of 2000 sets of data. I am trying to find the average
age of this set of employees when they were hired. The employees age
is within Column G, their hire date is in Column H, and the date the
data was produced (so that this can be used in the future without
distorting the facts at the time of the data) is in E1.

This formula works for calculating one person's age at the time of
hi

=G3-((E1-H3)/365))

(or, in english, =AGE-minus the difference between the date of the data
and the employees hire date, ie if an employee were hired 6 months from
now, this formula will show an age 6 months younger.

I need to somehow nest this inside a formula that will calculate, on
the fly, each person's age (out to 2000 people) and turn out an average
age of all the data when they were hired.

Is this possible within one cell?

How to use Visual Basic for Applications (VBA) to change UserForms in Excel

How to Programmatically Manipulate a UserForm

How to Programmatically Create UserForms in Visual Basic for Applications

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
β€’ How Can I Import a Fixed-Width Data File into Microsoft Excel?
β€’ How Can I Sort a Spreadsheet By Cell Color?
β€’ How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
β€’ How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
β€’ How Can I Read Custom Summary Information Properties for an Excel File?
β€’ How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Search For Values in an Excel Worksheet?
β€’ How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
β€’ How Can I Determine the Value of the Last Cell in an Excel Range?
β€’ How Can I Convert an Excel Serial Number to a Date and Then Back?
β€’ How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
β€’ How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
β€’ How Can I Update and Then Break All the Links in an Excel Spreadsheet?
β€’ How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Add a Background Picture to an Excel Worksheet?
β€’ How Can I Replace Numeric Values in an Excel Spreadsheet?
β€’ How Can I Copy Data From One Spreadsheet to Another?
β€’ How Can I Replace Text in an Excel Spreadsheet?
β€’ How Can I Set the Print Area in an Excel Spreadsheet?
β€’ How Can I Delete Specified Rows in an Excel Spreadsheet?
β€’ How Can I Color Every Other Row in an Excel Spreadsheet?
β€’ How Can I Copy Selected Columns From a CSV File to an Excel File?
β€’ How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
β€’ How Can I Export an HTA Table to Excel?
β€’ How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
β€’ How Can I Assign a Background Color to Cells in a Spreadsheet and Then β€œSum” Those Cells?
β€’ How Can I Sort a Row in an Excel Spreadsheet?
β€’ How Can I Remove the Password When Opening an Excel Spreadsheet?
β€’ How Can I Import an Excel Spreadsheet Into an Access Database?
β€’ How Can I Save a Table in an Access Database as a Spreadsheet?
β€’ How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
β€’ How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
β€’ How Can I Configure Excel to Autosave Every 5 Minutes?
β€’ How Can I Save an Excel Chart as a Picture?
β€’ How Can I Change the Font Color in Excel If a Specified Condition is Met?
β€’ How Can I Convert an Excel Spreadsheet to XML?
β€’ How Can I Set the Default File Path in Excel to a User’s Home Directory?
β€’ How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
β€’ How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
β€’ How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
β€’ How Can I Add a Total Row to an Excel Spreadsheet?
β€’ How Can I Create a Custom Date Format in Microsoft Excel?
β€’ How Can I Keep the Screen From Updating While Running an Excel Script?
β€’ How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
β€’ How Can I Determine the Background Color of a Spreadsheet Cell?
β€’ How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
β€’ How Can I Copy a Worksheet to a New Spreadsheet?
β€’ How Can I Use Windows PowerShell to Automate Microsoft Excel?
β€’ How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
β€’ How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
β€’ How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
β€’ How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
β€’ How Can I Create a New Excel Spreadsheet at Midnight Each Night?
β€’ How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
β€’ How Can I Convert a Number to a Date in Excel?
β€’ How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
β€’ How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
β€’ How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
β€’ How Can I Determine the Last Row in an Excel Spreadsheet?
β€’ How Can I Remove All the Formatting from an Excel Spreadsheet?
β€’ How Can I Change the Footer in an Excel Spreadsheet?
β€’ How Can I Add Additional Worksheets to an Excel Workbook?
β€’ How Can I Sort Worksheets in a Workbook?
β€’ How Can I Import a Fixed-Width File into Microsoft Excel?
β€’ How Can I Add a Hyperlink to an Excel Spreadsheet?
β€’ How Can I Build an Array from a Column of Data in Excel?
β€’ How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
β€’ How Can I Center Text in an Excel Cell?
β€’ How Can I Change the Background Color of a Cell Depending on the Date?
β€’ How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
β€’ How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
β€’ How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
β€’ How Can I Insert a Column into a Spreadsheet?
β€’ How Can I Save a Single Excel Worksheet to a CSV File?
β€’ How Can I Password-Protect an Excel Spreadsheet?
β€’ How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
β€’ How Can I Randomly Assign a Font to Characters in a Word Document?
β€’ How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
β€’ How Can I Count the Number of Sentences and Paragraphs in a Word Document?
β€’ How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
β€’ How Can I Tally All the Items in a Microsoft Word Document?
β€’ How Can I Reset the Revision Number of a Word Document to 1?
β€’ How Can I Retrieve Field Values in a Microsoft Word Document?
β€’ How Can I Change the Paragraph Case in Microsoft Word?
β€’ How Can I Insert a Manual Line Break into a Microsoft Word Document?
β€’ How Can I Search For and Modify Formatted Text in a Word Document?
β€’ How Can I Search For Red Text in a Microsoft Word Document?
β€’ How Can I Change the Default File Save Format in Microsoft Word?
β€’ How Can I Put the File Name in the Footer of a Microsoft Word Document?
β€’ How Can I Add a Page X of Y Footer to a Microsoft Word Document?
β€’ How Can I Save Each Paragraph in a Word Document as a Database Record?
β€’ How Can I Replace a Specified Font in a Microsoft Word Document?
β€’ How Can I Apply a Theme to a Microsoft Word Document?
β€’ How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
β€’ How Can I Insert a Date Field in Word?
β€’ How Can I Add a New Item to the Microsoft Word AutoCorrect List?
β€’ How Can I Clear All the Formatting From a Microsoft Word Document?
β€’ How Can I Change the Default Highlight Color for a Microsoft Word Document?
β€’ How Can I Run a Macro After Opening Up Word?
β€’ How Can I Center-Align a Picture in a Word Document?
β€’ How Can I Change an Existing Hyperlink in a Microsoft Word Document?
β€’ How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
β€’ How Can I Insert Text Into an Existing Microsoft Word Bookmark?
β€’ How Can I Get a Total Page Count for All the Word Documents in a Folder?
β€’ How Can I Set the Document Orientation in Microsoft Word to Landscape?
β€’ How Can I Replace Text in a Microsoft Word Document?
β€’ How Can I Search For (and Reformat) Highlighted Text in a Word Document?
β€’ How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
β€’ How Can I Get a List of Available Metadata for Microsoft Office Documents?
β€’ How Can I Add Multiple Tables to a Word Document?
β€’ How Can I Search a Word Document for All the Words in Double Brackets?
β€’ How Can I Add Centered Page Numbers to the Footer of a Word Document?
β€’ How Can I Create a Table and Fill the First Column With a Range of Dates?
β€’ How Can I Rename a Word Document Using the First Three Characters in That Document?
β€’ How Can I Search for and Highlight Words in a Microsoft Word Document?
β€’ How Can I Right-Align a Single Column in a Word Table?
β€’ How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
β€’ How Can I Change the Font Name and Size for an Entire Word Document?
β€’ How Can I Set Word’s Revision View Mode to Final?
β€’ How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
β€’ How Can I Set Word’s Default File Location to be the User’s Home Directory?
β€’ How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
β€’ How Can I Change File Locations for Microsoft Word?
β€’ How Can I Insert a Symbol into a Word Document?
β€’ How Can I Hide a Specific Toolbar in Microsoft Word?
β€’ How Can I Extract Word Paragraphs That Use a Specific Style?
β€’ How Can I Insert Files into a Word Document?
β€’ How Can I Convert 1,000 .RTF Files to Word Documents?
β€’ How Can I Add a Hyperlink to a Word Document?
β€’ How Can I Put the User Name into the Footer of a Microsoft Word Document?
β€’ How Can I Determine Which Version of Word is Installed on a Computer?
β€’ How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Add a Record to a Database Using Windows PowerShell?
β€’ How Can I Delete a Set of Records From an Access Database?
β€’ How Can I Be Notified if an Access Database File Exceeds a Specific Size?
β€’ How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
β€’ How Can I List All the Fields and Data Types in an Access Database?
β€’ How Can I Append a Value to a Specified Field For Each Record in a Database?
β€’ How Can I Add the Contents of a Group of Text Files to an Access Database?
β€’ How Can I Import a .CSV File into an Access Database?
β€’ How Can I List All the Access Database Files on a Computer?
β€’ How Can I Print a Microsoft Access Report?
β€’ How Can I Compact an Access Database?
β€’ How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
β€’ How Can I List All the Tables in an Access Database?
β€’ How Can I Determine Which Version of Access was Used to Create a Database?
β€’ How Can I Delete a Table from a Jet Database?

Microsoft Outlook
β€’ How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
β€’ How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
β€’ How Can I Set a Reminder on All My Outlook Appointments?
β€’ How Can I Schedule an All-Day Event in Microsoft Outlook?
β€’ How Can I Run a Script Any Time Outlook is Started?
β€’ How Can I Determine the Age, in Years, of an Outlook Contact?
β€’ How Can I Save the Attachments for All My New Outlook Messages?
β€’ How Can I Convert an Extension Number to an Actual Phone Number?
β€’ How Can I Create a New Folder in Microsoft Outlook?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Get a List of Appointments for a Specific Month?
β€’ How Can I Save Emails That Are More Than One Month Old?
β€’ How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
β€’ How Can I Save All My Contacts as VCards?
β€’ How Can I Delete Unread Emails That Are More Than 6 Months Old?
β€’ How Can I Randomly Select an Email From an Outlook Mail Folder?
β€’ How Can I List All the Meetings Scheduled By a Specified Person?
β€’ How Can I Filter Outlook Messages By Email Address?
β€’ How Can I List All the Members of a Microsoft Outlook Distribution List?
β€’ How Can I Determine the Follow-Up Status of Outlook Emails?
β€’ How Can I Tell If Any of My Contacts Have a Birthday This Month?
β€’ How Can I Delete All the Messages in My Sent Items Folder?
β€’ How Can I Start Outlook If It Isn’t Already Running?
β€’ How Can I Connect to the Junk Mail Folder in Outlook?
β€’ How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
β€’ How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
β€’ How Can I Delete All the Email Sent From a Specific Person?
β€’ How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
β€’ How Can I Get Total Size and Number of Items in an Outlook Folder?
β€’ How Can I Convert an Outlook Email Message into a Text File?
β€’ How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
β€’ How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Apply a New Template to a PowerPoint Presentation?
β€’ How Can I Configure PowerPoint to Print Handouts Instead of Slides?
β€’ How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
β€’ How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
β€’ How Can I Change the User Information in Microsoft Office?

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
• How Can I Import a Fixed-Width Data File into Microsoft Excel?
• How Can I Sort a Spreadsheet By Cell Color?
• How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
• How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
• How Can I Read Custom Summary Information Properties for an Excel File?
• How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Search For Values in an Excel Worksheet?
• How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
• How Can I Determine the Value of the Last Cell in an Excel Range?
• How Can I Convert an Excel Serial Number to a Date and Then Back?
• How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
• How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
• How Can I Update and Then Break All the Links in an Excel Spreadsheet?
• How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Add a Background Picture to an Excel Worksheet?
• How Can I Replace Numeric Values in an Excel Spreadsheet?
• How Can I Copy Data From One Spreadsheet to Another?
• How Can I Replace Text in an Excel Spreadsheet?
• How Can I Set the Print Area in an Excel Spreadsheet?
• How Can I Delete Specified Rows in an Excel Spreadsheet?
• How Can I Color Every Other Row in an Excel Spreadsheet?
• How Can I Copy Selected Columns From a CSV File to an Excel File?
• How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
• How Can I Export an HTA Table to Excel?
• How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
• How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?
• How Can I Sort a Row in an Excel Spreadsheet?
• How Can I Remove the Password When Opening an Excel Spreadsheet?
• How Can I Import an Excel Spreadsheet Into an Access Database?
• How Can I Save a Table in an Access Database as a Spreadsheet?
• How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
• How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
• How Can I Configure Excel to Autosave Every 5 Minutes?
• How Can I Save an Excel Chart as a Picture?
• How Can I Change the Font Color in Excel If a Specified Condition is Met?
• How Can I Convert an Excel Spreadsheet to XML?
• How Can I Set the Default File Path in Excel to a User’s Home Directory?
• How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
• How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
• How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
• How Can I Add a Total Row to an Excel Spreadsheet?
• How Can I Create a Custom Date Format in Microsoft Excel?
• How Can I Keep the Screen From Updating While Running an Excel Script?
• How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
• How Can I Determine the Background Color of a Spreadsheet Cell?
• How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
• How Can I Copy a Worksheet to a New Spreadsheet?
• How Can I Use Windows PowerShell to Automate Microsoft Excel?
• How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
• How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
• How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
• How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
• How Can I Create a New Excel Spreadsheet at Midnight Each Night?
• How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
• How Can I Convert a Number to a Date in Excel?
• How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
• How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
• How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
• How Can I Determine the Last Row in an Excel Spreadsheet?
• How Can I Remove All the Formatting from an Excel Spreadsheet?
• How Can I Change the Footer in an Excel Spreadsheet?
• How Can I Add Additional Worksheets to an Excel Workbook?
• How Can I Sort Worksheets in a Workbook?
• How Can I Import a Fixed-Width File into Microsoft Excel?
• How Can I Add a Hyperlink to an Excel Spreadsheet?
• How Can I Build an Array from a Column of Data in Excel?
• How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
• How Can I Center Text in an Excel Cell?
• How Can I Change the Background Color of a Cell Depending on the Date?
• How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
• How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
• How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
• How Can I Insert a Column into a Spreadsheet?
• How Can I Save a Single Excel Worksheet to a CSV File?
• How Can I Password-Protect an Excel Spreadsheet?
• How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
• How Can I Randomly Assign a Font to Characters in a Word Document?
• How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
• How Can I Count the Number of Sentences and Paragraphs in a Word Document?
• How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
• How Can I Tally All the Items in a Microsoft Word Document?
• How Can I Reset the Revision Number of a Word Document to 1?
• How Can I Retrieve Field Values in a Microsoft Word Document?
• How Can I Change the Paragraph Case in Microsoft Word?
• How Can I Insert a Manual Line Break into a Microsoft Word Document?
• How Can I Search For and Modify Formatted Text in a Word Document?
• How Can I Search For Red Text in a Microsoft Word Document?
• How Can I Change the Default File Save Format in Microsoft Word?
• How Can I Put the File Name in the Footer of a Microsoft Word Document?
• How Can I Add a Page X of Y Footer to a Microsoft Word Document?
• How Can I Save Each Paragraph in a Word Document as a Database Record?
• How Can I Replace a Specified Font in a Microsoft Word Document?
• How Can I Apply a Theme to a Microsoft Word Document?
• How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
• How Can I Insert a Date Field in Word?
• How Can I Add a New Item to the Microsoft Word AutoCorrect List?
• How Can I Clear All the Formatting From a Microsoft Word Document?
• How Can I Change the Default Highlight Color for a Microsoft Word Document?
• How Can I Run a Macro After Opening Up Word?
• How Can I Center-Align a Picture in a Word Document?
• How Can I Change an Existing Hyperlink in a Microsoft Word Document?
• How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
• How Can I Insert Text Into an Existing Microsoft Word Bookmark?
• How Can I Get a Total Page Count for All the Word Documents in a Folder?
• How Can I Set the Document Orientation in Microsoft Word to Landscape?
• How Can I Replace Text in a Microsoft Word Document?
• How Can I Search For (and Reformat) Highlighted Text in a Word Document?
• How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
• How Can I Get a List of Available Metadata for Microsoft Office Documents?
• How Can I Add Multiple Tables to a Word Document?
• How Can I Search a Word Document for All the Words in Double Brackets?
• How Can I Add Centered Page Numbers to the Footer of a Word Document?
• How Can I Create a Table and Fill the First Column With a Range of Dates?
• How Can I Rename a Word Document Using the First Three Characters in That Document?
• How Can I Search for and Highlight Words in a Microsoft Word Document?
• How Can I Right-Align a Single Column in a Word Table?
• How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
• How Can I Change the Font Name and Size for an Entire Word Document?
• How Can I Set Word’s Revision View Mode to Final?
• How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
• How Can I Set Word’s Default File Location to be the User’s Home Directory?
• How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
• How Can I Change File Locations for Microsoft Word?
• How Can I Insert a Symbol into a Word Document?
• How Can I Hide a Specific Toolbar in Microsoft Word?
• How Can I Extract Word Paragraphs That Use a Specific Style?
• How Can I Insert Files into a Word Document?
• How Can I Convert 1,000 .RTF Files to Word Documents?
• How Can I Add a Hyperlink to a Word Document?
• How Can I Put the User Name into the Footer of a Microsoft Word Document?
• How Can I Determine Which Version of Word is Installed on a Computer?
• How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Add a Record to a Database Using Windows PowerShell?
• How Can I Delete a Set of Records From an Access Database?
• How Can I Be Notified if an Access Database File Exceeds a Specific Size?
• How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
• How Can I List All the Fields and Data Types in an Access Database?
• How Can I Append a Value to a Specified Field For Each Record in a Database?
• How Can I Add the Contents of a Group of Text Files to an Access Database?
• How Can I Import a .CSV File into an Access Database?
• How Can I List All the Access Database Files on a Computer?
• How Can I Print a Microsoft Access Report?
• How Can I Compact an Access Database?
• How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
• How Can I List All the Tables in an Access Database?
• How Can I Determine Which Version of Access was Used to Create a Database?
• How Can I Delete a Table from a Jet Database?

Microsoft Outlook
• How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
• How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
• How Can I Set a Reminder on All My Outlook Appointments?
• How Can I Schedule an All-Day Event in Microsoft Outlook?
• How Can I Run a Script Any Time Outlook is Started?
• How Can I Determine the Age, in Years, of an Outlook Contact?
• How Can I Save the Attachments for All My New Outlook Messages?
• How Can I Convert an Extension Number to an Actual Phone Number?
• How Can I Create a New Folder in Microsoft Outlook?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Get a List of Appointments for a Specific Month?
• How Can I Save Emails That Are More Than One Month Old?
• How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
• How Can I Save All My Contacts as VCards?
• How Can I Delete Unread Emails That Are More Than 6 Months Old?
• How Can I Randomly Select an Email From an Outlook Mail Folder?
• How Can I List All the Meetings Scheduled By a Specified Person?
• How Can I Filter Outlook Messages By Email Address?
• How Can I List All the Members of a Microsoft Outlook Distribution List?
• How Can I Determine the Follow-Up Status of Outlook Emails?
• How Can I Tell If Any of My Contacts Have a Birthday This Month?
• How Can I Delete All the Messages in My Sent Items Folder?
• How Can I Start Outlook If It Isn’t Already Running?
• How Can I Connect to the Junk Mail Folder in Outlook?
• How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
• How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
• How Can I Delete All the Email Sent From a Specific Person?
• How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
• How Can I Get Total Size and Number of Items in an Outlook Folder?
• How Can I Convert an Outlook Email Message into a Text File?
• How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
• How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Apply a New Template to a PowerPoint Presentation?
• How Can I Configure PowerPoint to Print Handouts Instead of Slides?
• How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
• How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
• How Can I Change the User Information in Microsoft Office?

InputBox Function

As you may, or may not know, there are multiple types we can use. The code below will show you some of these types. We will kick off we the most common use of an INPUTBOX, collect text.


	VB:
	
 StandardInputBox() 
    Dim strName As String 
     
     'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
    strName = InputBox("Enter you name.", "NAME COLLECTOR") 
     'Exit sub if Cancel button used or no text entered
    If strName = vbNullString Then Exit Sub 
     
    MsgBox "Hello " & strName 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now we can look at how we can use Application.InputBox with different types.TypeMeaning0A formula1A number2Text (a string)4A logical value (True or False)8A cell reference, as a Range object16An error value, such as #N/A64An array of values

	VB:
	
 NumbersOnly() 
    Dim dNum As Long 
     
     'expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
    dNum = Application.InputBox("Enter you age.", "AGE COLLECTOR", , , , , , 1) 
     'Exit sub if Cancel button used
    If dNum = 0 Then Exit Sub 
     
     'As we have used Type 1 Inputbox, Excel will handle any text entered
    MsgBox "You are " & dNum & " Years old." 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 RangeAsObject() 
    Dim rRange As Range 
    Dim lReply As Long 
     
     'We use On Error so we ignore run time errors
    On Error Resume Next 
     'expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
    Set rRange = Application.InputBox("With you mouse, select a range of cells.", _ 
    "RANGE COLLECTOR", , , , , , 8) 
    On Error Goto 0 
     
     'Check if range is valid
    If rRange Is Nothing Then 
         'If range not valid, ask if they wish to retry
        rRange = MsgBox("Non valid range. Try again?", vbOKCancel + vbQuestion) 
        If rRange = vbCancel Then 'No retry
            Exit Sub 
        Else 'retry
            Run "RangeAsObject" 
        End If 
    Else 
        MsgBox rRange.Address 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now let's see how we can the INPUTBOX to evaluate a chosen range with an expression collected from a user.

	VB:
	
 CollectExpressions() 
    Dim strExpression As String 
    Dim rRange As Range 
     
    On Error Resume Next 
    Set rRange = Application.InputBox("With you mouse, select a range of cells to evaluate.", _ 
    "RANGE COLLECTOR", , , , , , 8) 
    On Error Goto 0 
     
    If rRange Is Nothing Then 
        rRange = MsgBox("Non valid range. Try again?", vbOKCancel + vbQuestion) 
        If rRange = vbCancel Then 'No retry
            Exit Sub 
        Else 'retry
            Run "CollectExpressions" 
        End If 
    End If 
     
    strExpression = InputBox("Enter you expession.E.g. >5 or

Hi all,

I'm trying to chart some data from workings I have generated. I wish to keep the same chart (and data source range) should I change certain assumptions of the variables for ease of reference.

Essentially I wish to show the total value of investments for a person for the period up to when they hit 65 (retirement age). I do not wish to show any data beyond the age of 65.

Is there a way that I can limit the chart to display data to reflect this? e.g. if someone is 55 now I wish to only show 10 years of projections and the chart to cut-off at 65, but if I change the date of birth to 1982 the chart would display 35 years of projections?

The attached shows roughly what my problem is. At present the chart is reflecting the blank cells as a value of 0, whereas I don't wish to see it in the chart if the value is blank.

Any help greatly appreciated.

P.s. I have included a formula in each of the cells to return a blank cell if the age goes over 65. The chart is based off data from cells A5:C52.

Hello everyone, first of all, thank you for taking your time to check my Thread out.
This is, in my opinion, a rather complicated matter.
Bottom line is, I want to create a dynamic filter with a combo box that will be used with the countifs function.

Well, here's what I got.
1 Excel 2010 file (.xlsx)
4 Workbooks ( inside this same file)

2 Workbook actually matter, one is named Database and the other is named Plotting.

In the Database Workbook I have over 30 columns with different things about each entry (line), it goes as follow.
a1 name, b1 position, c1 age, d1 ***, e1 earning range and so on.

In the second workbook, Plotting, I have a matrix (I designed myself) and it has 16 plotting possibilities, if the person matches x in the x vector on the plotting and y in the y vector of the plotting then the person is x y in the plotting criteria.

So far, so good, I managed to get everyone in the company in the plotting scenario, my "baby" (as I like to call this mess of a excel file) is doing good, BUT, now, the human resources needs to filter the plotting values based on over 20 different criteria, ex:

the plotting matrix has everyone at the moment, BUT, I want to see only those who have between x and y of age, x and y of earning range and are of a specific position inside the company.

I managed to get as far as to make the combo box and to separate all of the criteria that may be used and upon selected it will use on the countifs formula, the problem is, I must specify a range and I can't make the whole workbook my range, it simply doesn't work if I do so.

What I believe it would help me it would be if I could have a cell determine the range of a countifs, I'd like to do this =countifs(a1;a2;b1;b2) where a1 would be defined upon the selection of a2 beforehand and it would contain the range accordingly with the contents of a2.

I can't use any VB, I should I only use Excel formulas and unfortunately I cannot share the file.

Thanks for reading this post.

I have a range of 2000 sets of data. I am trying to find the average
age of this set of employees when they were hired. The employees age
is within Column G, their hire date is in Column H, and the date the
data was produced (so that this can be used in the future without
distorting the facts at the time of the data) is in E1.

This formula works for calculating one person's age at the time of
hire:

=G3-((E1-H3)/365))

(or, in english, =AGE-minus the difference between the date of the data
and the employees hire date, ie if an employee were hired 6 months from
now, this formula will show an age 6 months younger.

I need to somehow nest this inside a formula that will calculate, on
the fly, each person's age (out to 2000 people) and turn out an average
age of all the data when they were hired.

Is this possible within one cell?

OK so I'm completely new to all this but I've taken on a challenge to try to create a spreadsheet to evaluate fitness data. I'll try to explain this the best that I can.

Males and Females are evaluated on different critera as are different age groups. My end goal is to figure out what percentile each candidate falls in.
For example MALE --> 29 years old --> 60 pushups --> puts him in the 80th percentile. I have hard copies of all the tables show what percentile each candidate falls based on age and gender. Is this even possible? Should I create multiple spreadsheets? I guess what I am thinking is that if I enter MALE on a drop down menu the formulas for the rest of the sheet would change...then when I select the age range the formulas will change again. Then they will pull from the corresponding table. I hope this makes any sense at all. If anyone can offer any advice it would be greatly appreciated.
Thanks in advance.

Josh

I know this is an age old problem in Excel and I hope somebody has figured it out.
I have four adjacent columns in a worksheet
The first column is blank and I have a macro that writes data to the rows in this column

The second column has the following formula:
The third column has the following formula:
And the third column has the following formula:
Anyway, it doesn't really matter what's in these columns because when data is written to the first column, the last column is
supposed to change and subsequently the other two columns. But they don't, they show some residual value in memory and the
only way to get the formula to update the results is by placing the cursor in the formula bar and hitting enter.  I tried
manually trying to recalculate by using every form of the F9 function (i.e. with Shift, CTRL, ALT) and nothing works. I tried
using several macros and nothing works:

objworksheet.Range("H13:J27").Calculate
objworksheet.Calculate
objworksheet.Cells(13, 5).Calculate
I even tried using a trick I read somewhere:
objworksheet.EnableCalculation = False
objworksheet.EnableCalculation = True
Anybody has any ideas?

Hi All!

This is the only thread I could find to assist me - but my tries do not get my VBA code to work:

HTML Code:

I have exactly the same situation:

In the range A1:H89 I have expanding and retracting values - all of which is determined by the numbers in column A. Column A is simply A1+1 copied down. The value of A1 is determined by the value of another cell, based on age.

If the value in column A exceeds a specific figure (let's say 50), formulas in columns B - H result in answers of "" (this is "nothing"?). Assume that the value of 50 is reached in A20.

All cells from A21 (inclusive) down, including B21 - H21, will have the values "" in them and show up as nothing on my screen.

When I print, I'd like to only print those cells where data is, i.e. cells A1:H20.

This is what I have so far:


	VB:
	
 Drukwerk() 
     '
     ' Drukwerk Macro
     ' Macro recorded 08/06/2007 by Home
     '
     
     '
    With ActiveSheet.PageSetup 
        .PrintTitleRows = "$7:$9" 
        .PrintTitleColumns = "" 
    End With 
     
    LastRow = Range("A" & Rows.Count).End(xlUp).Row 
     
    ActiveSheet.PageSetup.PrintArea = "A1:H" & LastRow 
     
     '    ActiveSheet.PageSetup.PrintArea = "$A$1:$H$89"
    With ActiveSheet.PageSetup 
        .LeftHeader = "" 
        .CenterHeader = "" 
        .RightHeader = "" 
        .LeftFooter = "" 
        .CenterFooter = "" 
        .RightFooter = "" 
        .LeftMargin = Application.InchesToPoints(0.748031496062992) 
        .RightMargin = Application.InchesToPoints(0.748031496062992) 
        .TopMargin = Application.InchesToPoints(0.78740157480315) 
        .BottomMargin = Application.InchesToPoints(0.78740157480315) 
        .HeaderMargin = Application.InchesToPoints(0.511811023622047) 
        .FooterMargin = Application.InchesToPoints(0.511811023622047) 
        .PrintHeadings = False 
        .PrintGridlines = False 
        .PrintComments = xlPrintNoComments 
        .PrintQuality = -2 
        .CenterHorizontally = True 
        .CenterVertically = False 
        .Orientation = xlPortrait 
        .Draft = False 
        .PaperSize = xlPaperA4 
        .FirstPageNumber = xlAutomatic 
        .Order = xlDownThenOver 
        .BlackAndWhite = False 
        .Zoom = 100 
        .PrintErrors = xlPrintErrorsDisplayed 
    End With 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code still prints the full recorded macro's range of A1:H89.

Any help as to how to rectify this, please?

And my screen also flickers quite a lot while running my macro. What could cause that?

Regards

Harry

I have a log I created using a table that tracks when a project plan has been last updated.

Column A has the project name, Column B has the following MATCH formula: =MATCH("x",Table2[[#This Row],[21-Apr]:[31-May]],1)

So 21-Apr and 31-May are column headers.

So how it works is that when a project has been updated I put an "x" in the cell corresponding to the date it was updated, and I get a value back in the formula in column B showing how many cells out it is, I use this number as a lookup to find what date that corresponds to, and then I subtract today's date from the last updated date to get an "age" of when the last plan was updated.

What's happening is that it works fine unless an "x" is put in a certain range of cells, from 11-May thru 25-May that returns an invalid number. So if a project is updated on both 11-May and 27-May (today's date) I should end up with a 0 for the age, but I'm getting a value from the 11-May cell but not the 27-May cell. In other words, the MATCH formula is finding the "x" in the 11-May cell but not in the 27-May cell.

I've looked at the formatting of those cells, tried changing the column headers, everything else that I can think of but nothing is working.

Hope my explanation of the problem is clear enough.

Thanks!

Hiya,

I've some code which works kinda to delete around 100 blanks out of a range of about 500 rows.....

Code:
Private Sub CommandButton1_Click()
Dim NumCount As Long
Dim c As Long
Dim Limit As Long
    Cells.Select
    Selection.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A2").Select
    Do While Not Selection.Value = ""
        Selection.Offset(1, 0).Select
    Loop
    Limit = Cells(Rows.Count, 1).End(xlUp).Row
    For c = 2 To Limit
    If Cells(c, 1).Text = "" Then
        Rows(c & ":" & Limit).Delete shift:=xlUp
    End If
    Next c
    Cells.Select
    Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Range("A1").Select
End Sub
The only problem is that it's inefficient. and takes an age..... showing the treatsie "Calculating 100%" for a good 10 seconds.

Is this due to my code, or due to the workbook? There does not appear to be any formulas in the sheet where this code is activated.

Thanks!

I have a column showing the ages of a number of people. I want to count the
number of people who a

Over 65
60 to 65
and 55 to 60

The countif formula I tried is not working. Thank you in advance for any help.

James

Hey everyone, I haven't been able to figure out how to pull this off:
I've got a database of about 3,000 patients from which I will soon need to extract data for research.
Right now I am focusing on three of my columns, ***, AGE and RACE.
Using a pivot table I can see how many M's or F's are AA or Cau, and totals of both ***'s and all race's and so on.
But I cannot figure out how to, either by using a worksheet formula(s) or pivot table, display the distribution of patients through a set age-range.
My goal is to have a table that will show how many AA's, Cau's, etc and how many M's and F's of each race are between the ages of 10-20, 21-30. 31-40 and so on.

I have not been succesfull with any counting formulas, and in the pivot tables have not found a way to control how the AGE column is displayed, so as it is I have on row for virtually every age between 10 and 90. Not ideal.

Any help would be GREATLY appreciated, thank you!

-Sean


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