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

Free Microsoft Excel 2013 Quick Reference

Columns display numbers instead of letter Results

I am running 2007 and the top of the columns display numbers instead of letters. How do I fix this?

Thanks for your help.

What is the best way to refer to spreadsheet columns by number? Ideally, the columns would actually display numbers instead of letters at the top of the spreadsheet. Strictly speaking, though, I need to be able to refer to a range of cells based on a number for each column in a VBA script.

I would rather not map each column (A through MC) to a number for the program's purposes. Is there a simpler function that will either let me refer to ranges by column number, or translate the column number into a column alphabetic reference?

Thanks.

I received a workbook from a client and I notice the column Header displays numbers not letters: 1 2 3 4 instead of: A B C D

If I open a new Workbook while this workbook is open (using the toolbar icon), the new workbook also displays numbers in the column header.
If I close teh file and reopen Excel, everything is normal.

Is this just an error? Has anyone seen this before? Is there code that will switch the column header from numbers back to Letters.

I can send the file if that helps.

Thanks

The column headers are displaying numbers (1,2,3) instead of letters (A,B,C),
which is making it difficult for me to type in formulas. Ultimately, I'd
like to have a column at the right end of my spreadsheet that totals a
specific set of columns *within each row*. How do I do this?

I am using Excel that came with Microsoft Windows XP

Excel has suddenly begun to display all column headings as numbers instead of
letters. Does anyone know why, but more to the point, how to change it back
to letters? Thanks.

Does anyone know how to correct a worksheet display? I opened a file and the worksheet display numbers for the columns instead of letters. All of the rows are numbered as well, so any formulas created do not work.

i'm betting this is a really stupid problem but here it goes.

i CANNOT figure out why my column headings are shown as numbers instead of the typical letters. i am running windows 2000 pro and office 2000 pro.

does anyone know how to make the column headings display as letters instead of numbers? it's very extremely annoying.

thanks!

Hi there,

I'm trying to create a formula to get Excel 2003 to do basically this:

if the value of column i = 0 to 5 the letter value shown is L
if the value of column i - 6 to 11 the letter value shown is M
if the value of column i - 12 to 17 the letter value shown is H

basically i have columns F, G, H that have numerical values and a formula that adds those together to come up with a number 0 to 17. I want that number, based on the idea above, to visualize a letter instead of the number

for example:

F = 4
G = 4
H = 7
I = 15 so it would show up as H in the cell.

something like this:
=SUM(F12:H12) ----- THEN add the other part where if the answer is 0-5 it displays L, etc.

Thanks for any help and input!

Stephanie

Hello all,

Will you please help me modify the following/or find a code to display all of the combinations of 3 groups of text such that each combination has exactly 1 letter from each group and combinations are in order Group 1 Group 2 Group 3 for example choock

Group 1 b, c, ch, d, f, g, h, j, k, l, m, n, p, qu, r, s, sh, t, th, v, w, y, z

Group 2 a, e, ee, i, o, oo, u

Group 3 b, c, ck, d, f, g, h, j, k, l, m, n, p, r, s, sh, t, th, v, w, x, y, z

The code below is pasted by WIGI http://excel.bigresource.com/Track/excel-qqlf8Daz/

The input box is ideal my application
Can this code be modified to 3 Groups instead of 4 Groups?
Can this code be modified to order the combinations Group 1 Group 2 Group 3?


	VB:
	
 comboofletters() 
     
    Dim str1 As String, str2 As String, str3 As String, str4 As String 
    Dim i As Integer, j As Integer, k As Integer, l As Integer 
     
    str1 = InputBox("Give combination of letters number 1") 
    str2 = InputBox("Give combination of letters number 2") 
    str3 = InputBox("Give combination of letters number 3") 
    str4 = InputBox("Give combination of letters number 4") 
     
    Columns("A").ClearContents Range("A1") = "Combinations:" 
    For i = 1 To Len(str1) 
        For j = 1 To Len(str2) 
            For k = 1 To Len(str3) 
                For l = 1 To Len(str4) 
                    Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Mid(str1, i, 1) & Mid(str2, j, 1) & Mid(str3, k, 1) &
Mid(str4, l, 1) 
                Next 
            Next 
        Next 
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you all for your help. I apologize in advance in case my thread is not formatted correctly for example not posting the code into a separate window.

Regards,

Aeroron

Aloha,

I would like to have a student's current overall grade be automatically calculated and displayed as I enter the test scores and extra credit points over the semester, first in percent and then in letter grade.

I have it set up so that the percent is calculated based on the total number of points likely to be obtainable at the end of the semester. But if they have only taken two tests and one extra credit assignment, it takes the total of those and divides it by the semester's total possible, instead of just the two tests and extra credit total possible. (ps: I do not know how many extra credit columns or number of possible points I will have this early in the semester)

Also, how then do I set up a formula to translate that percentage into a letter grade based on A=90-100, B=80-89, etc.?

Thank you for any help or direction you can give me.

renι

Hello, will someone please tell me how to make my columns show LETTERS instead of numbers... I've must've have changed it somehow and now it shows numbers (for example A1 displays 11) Help!!!.
Any help will be greatly apreciated. Thanks

Is there a way to find out the Column Letter and Row Number from the ActiveCell?

What I'm trying to do is I have a Vacation Tracker Program that I'm writing some VBA code for so that the user can enter a name into a user form and it automactically adds the name and necessary fields to the spreadsheets. The only area I'm having a problem with is, one sheet shows totals of the most pertinent information from another sheet and is more printable than the other form. The problem I'm having is that when a new user is added a section is copied and pasted and then the name is changed by finding the last row and then by using offset to get to that cell. I also used offset to find the cell that needs to show on the other sheet. The problem I'm having is if I set the activecell on the first sheet (found by using offset) equal to a variable and then have the second sheet equal to that variable I get the number on the first sheet, but the second sheet doesn't update when that number changes. The list of names that are hard coded into the program use the following formula to get those numbers and they do update when the numbers on the first sheet change: =Sum(Total!R386).

So if I could find out the column letter and row number of the activecell then I could code that in to a similiar setup. I don't know how I would locate the cell I need to display information on the second sheet any other way then by using offset, which doesn't give me the exact cell name. If anyone else has a better idea of how to do this instead of how I'm trying to do it, that would also be great.

Thanks,

Mickie

excel newbie here.

I was able to cut & paste tabular data from the web to an excel sheet. Each column under its own letter. Looks great. But one of the columns has data that has this format 32:31:00 I want this treated as text particularly when I import into an Access DB. But when I do format|cell|text it changes the contents to 1.354861111 rather than displaying 32:31:00. What I import this into Access DB it brings along this wierd 1.354861111 instead of 32:31:00.

Note: when the cell is displaying 32:31:00 and if I click on it it changes to
1/1/1900 8:31:00 AM This is what Access DB sees. Don't want that. Want to see: 32:31:00. Actually 32:31, that is drop of the trailing zeroes.

If I leave it as excel formatted it then Access treats it as some date/time field although the Access field is defined as text.

Another column has data that looks like 7:02 or 24:02 I want this to be treated as text. Although it does represent mm:ss in this case.

I am manually searching a worksheet to find partial strings within the data, and need to be able to sort the displayed results by cell column. It's a pretty large worksheet, with cells out to the ZL column. The "Find" results window appears to allow sorting in this manner with clickable display column headers, but when I click to sort the "cell" column in the find results display window, it reorders the results by row number as follows:
$CP$15
$GE$23
$BK$29
$FB$31
$DP$35
$GE$47
$S$48

Is there a simple way to sort the find results by the column letters so that I get an ordered display like this:
$BK$29
$CP$15
$DP$35
$FB$31
$GE$23
$GE$47
$S$48

?

My goal is to identify columns that contain two or more instances of the partial string that I'm searching for, and by sorting the find results by Worksheet Column instead of Row I can easily spot them in the display.

I've got an Access database which I've exported to Excel. This database contains the names and addresses of hundreds of hospital adminstrators across the country. I'm sending out a form letter using mail merge feature in Word. Small problem: the access database given to me, does not contain the transplant type so upto now I have been manually typing that in.

Proposed Solution: I thought about creating a new column called "Organ Transplant Type" in the exported database mentioned earlier. So for each hospital, their specialty will be listed in that cell. However, some of these hospitals specialize in more than one - in fact several. So typing it out by hand would be a bit tedious.

For example:
1) Adult heart
2) Pediatric heart
3) Adult Liver
4) Pediatric Lung
5) Adult Autologous

etc....

So instead of typing each organ out, i'd rather assign a code. So for a fictitious hospital, call it "Kramer Hospital" - they might specialize in adult heart, liver & auotoglous. So in the inserted column in Excel, they would show 1; 3; 5. Then I need some way of translating those "codes" if you will so that when it merges in Word, the proper organ types are displayed rather than meaningless numbers.

I don't know anything about VLOOKUP or HLOOKUP. Would either function be able to "translate" those numbers into the proper names (words) so that BEFORE i do the mail merge in Word, the correct organ transplant types are displayed? If so, how would I do that step by step?

I'm just trying to think of the easiest way to incorporate the data merge into the form letter. If only the original Access db had the organ transplant type in, all this would be infinitely easier!!!

I'm extremely new to VBA and don't even know how to program, so your help would be greatly appreciated.

I have a range in my monthly MealsServed worksheet that is 31 rows deep and 28 columns wide. A Cell outside this range (say, at F2) has the value of the 1st day of the current month. Column A in this range (beginning at, say, A5) contains the numbers 1-31 sucessively (to accommodate the maximum number of days in a month. (Hmmm, maybe I should make these actual dates instead of just numbers?).

I need a formula that I can copy down (in a 29th column) that will display the the letter 'w' for each row whose date (using the month and year at F2 and the number at A5:A35) is a Wednesday. I'm thinking something like:
=if(DayOfTheMonthAsIndicatedInColumnA="Wednesday","w","")

Can someone suggest a way to do this?

Actually, it would be really cool if I could just enter the date in F2 each month and Excel/VBA would dynamically create the range exactly as long as I need it! (But I'll be ticked silly just to be able to identify the Wednesdays, for now.)

We are using Outlook 2002 and 2003 to create tasks for many different people.
Each person adds notes to their tasks. To create a weekly report, we export
the tasks from each person into 1 CSV file per person, and then import each
CSV file into a single file in Excel 2003 ver (11.8117.8122) SP2.

When we have a large amount of notes for a task, the import into Excel
overflows the "Notes" cell/column, and the overflow text spews into other
cells like "Subject", "Start Date", etc. This one large task might take up 20
or 30 rows in Excel instead of 1. This make a horrible looking report!

We TRIED to export from Outlook directly into Excel, but that process only
captures about 200-300 characters of the notes in the task, so it is quite
worthless. That was why we went the CSV file route.

Questions a

1) Excel help indicates "Length of cell contents (text)" as: "32,767
characters. Only 1,024 display in a cell; all 32,767 display in the formula
bar".
Why would a task that has 31283 characters (with spaces) overflow the cell,
if the limit is 32767? One task that has 30038 characters was handled
properly upon import.

2) Is there a way to force Excel to truncate the import so the overflow does
not occur?

3) What is the BEST way to handle large text task imports that have notes
greater than the limit?

My test cases were several tasks created with the following numbers and
letters to get the desired character count, and then saved to different tasks
with different word counts:

---
test3

1234567890 1234567890 1234567890 1234567890 1234567890 1234567890
abcdefg hijklmn opqrstuvwxyz abcdefg hijklmn opqrstuvwxyz abcdefg hijklmn
opqrstuvwxyz

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?

Hi guys,

This is an odd one, I hope you can help with. I'm not sure if it needs a lookup code or what...

See attached spreadsheet.

In the column 'serial no', we can get any number of different results, but these always start with a two or three letters, which I have a list of - there's only about ten different possibilities (i./e. VC, DAF, CN etc etc). The digits that follow these two/three letters are always whole integers, which just progress sequentially, as in the example attached.

What I need is a cell (to the side of the main data), which shows the last VC, DAF, CN number in the list... i.e. in the attachment, for VCs we've got up to VC442168, and for CNs we've also got up to CN510240. So I'd like to be able to make a box which in logical steps goes something like; Look in column C, for codes beginning with VC, look for last instance of this VC in the column, and display this in cell L39 (for example, could be any cell I choose). If the code was working correctly for VCs, it would display VC442168 in cell L39.

I'd then want to do a similar code for DAF, CN, etc etc, but this will produce in a separate box (i.e. L40, L41, L42), so I'll just copy the code above replacing the letters with DAF instead of VC, and so on and so forth.

Is there a way of getting it to do this?

thanks for your help!

Km


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