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

Free Microsoft Excel 2013 Quick Reference

Spreadsheet Alarms

I have a spreadsheet that has many events throughout the year. Is there a way
that I can set an alarm to remind me of an upcoming event, say a week in
advance??

Thanks in advance for your help.


Post your answer or comment

comments powered by Disqus
Hi guys

I have a spreadsheet where in column A I enter a date. The date expires after 20 UK working days (4 weeks), I want the spreadsheet to give me an indication/alarm or something that the date has expired, maybe by turning the whole row a different colour or something. Any ideas?

Thanks in advance

How can I trigger an alarm type event in Excel?

I am setting up a spreadsheet containg information relating to individuals who have subscribed to a magazine produced by my company. One of the fields contains a date value, relating to when the individual's subscription expires.

What I would like to do is have some form of event which compares the expiry date with the current system date. Should the expiry date equal or exceed the current system date, I want to create some form of alarm event (possibly in Outlook?).

Can someone help?

Cheers,

I don't know if this is possible, but I figured I would ask:

I have a spreadsheet that I work with that shows the schedules of my
students; each different place my student goes has its own row, so a student
may have many rows if they have many activities. currently, i auto filter the
day column to the current day so i can see what students have activities on
that day, and when/where they are. i was wondering if i could set an alarm on
the column labeled 'start time' that would pop a window up when any value in
that cell matched the system time... is this possible?? thanks,
--
Laura

I have an issue with Excel 2007. I have been told that my spreadsheets are growing exponentially, and I had no idea it was happening. Apparently when I cut and paste, delete and use other basic functions, this data is still being stored in the background of the spreadsheet causing it to grow at alarming rates. This is causing the spreadsheet to "freeze" and in worst case scenarios, causing the computer to lock up.
Does anyone have any ideas why this is happening, and how to prevent it from happening?
Thanks,

I generated html for an Excel spreadsheet using Excel's publish function. I
then copied this html into a web gem on my web page and published the page.
I am running IE6 and XP (Office 2000) as well as Norton Internet Security.
The web page loads fine for me.

However, a couple of my users have indicated that they see a square with a
dot in it. One user is running IE6/WinME, another AOL's browser/IE6/Win98.
The WinME user also has Zone Alarm but he turned it off. The other user says
she does not have any firewall s/w running. The WinME user tried accessing
this page from his work computer (it loaded fine....think that was an IE6/XP
confiig) as well as his girlfriend's PC (it also loaded fine...also think it
was IE6/XP).

So why would it appear this spreadsheet does not display for what appears to
be older versions of o/s? On the surface, I would have assumed the problem
was related to firewall s/w, accelerator s/w, IE settings. Or is this an
Excel html problem? Note I also uploaded the html spreadsheet file and
placed a link on the page to see if the different "implementation" of the
spreadsheet might make a difference. It doesn't. Can anyone help provide
guidance into how to troubleshoot this? Or is there something I can or
should be doing to ensure this is not a problem for a user (regardless of
what they are running)?

Note I have this same discussion going on under IE since I don't know if the
root problem is with excel or IE. I have received one post back suggesting
autovue is installed on problem PCs causing incorrect file associations.
Users in question do not have autovue installed. Also, I have other
spreadsheets
available from the same web site. The xls itself was uploaded and a link
provided to it. Neither user has a problem accessing spreadsheets made
available in this manner.

Hello Smart Friends,

Brief overview of my need:
We maintain a time tracker for time spent on each activity and at the end of the day the time collected in spread sheet needs to be entered in SAP Website.

Can you please help me with below :
I already have time tracker attached wit this post

Help 1 : I want to trigger an Alarm at 10:30 PM everyday on the same file as a reminder to enter spreadsheet time collected on a whole day to SAP Website.

Help 2 : I want to set a tab in the below blank space wherein I click and it takes to my SAP Website.

Thanks in advance...
Charmi

Hi Guy & Girls,

Have a current situation with an alarms list from DCS system at work output file is .CSV.

Each alarm has specific tag and all have priority ratings

What I would like is a template type WorkSheet with possibly some code/VBA where by when the alarms list in copied and pasted from DCS to excel file all high priority alarms are flagged i.e. cell turns red or something to that effect

Included is the worksheet:
The Tag Name is unique and does not appear anywhere else.
Element Number, Alarm Message, Priority, Action, Responsibility and Comments are not unique.

Not all alarms will occur in a daily process (i.e. maybe only 3000 and some may never alarm) thus what I need is a template type file, where by the alarms that occur on a specific date can be copied from the DCS into this file.

Once copied they will then flag each appropriate Tag Name and also if possible to have a counter for the amount of time an alarm has being processed.

Shown here is a link to Dropbox with the Excel file attached

https://www.dropbox.com/s/j52xnwt6fn...ll%20List.xlsx
Dont have much knowledge of VBA but would appreciate push in the right direction. Using Excel 2007 also

Thanks for your help in advance.

First of all, thank you all for your help! Here is the scenario:

I have a primary excel spreadsheet that I work from. The architecture is as follows:

Sheet 1: Called "Information"
Column A: Name
Column B: Date of Birth
Column C through Z: Various bits of information.

Sheet 2: Called "Master"
Cell B1: Contains the date and time of last update from the VBA I am asking for below.

On a weekly basis I get sent a "Update" spreadsheet that is constructed the same way as my primary. This is what I would like to do with some VBA:

From my primary sheet I run the VBA and it opens a pop up that allows me to select the updated worksheet. Next it cycles through both worksheets (Primary and Update). It compares Column A and B, if it finds a match it updates columns C through Z from the "Update". In order to get a match cell A1 and B1 of the primary worksheet has to match Cell A1 and B1 of the update sheet exactly.

The second thing I would like it to do is if the update sheet contains a new entry...in other words the update sheet has a row that does not match the primary it copies the row from the Update sheet to the Primary. In this way, the Primary sheet is always growing with new information and updating any old information it matches.

The last thing I would like to do is copy the current date and time after the comparison is complete to the Primary workbook to sheet "Master" in cell B1. This way I always know the time and date of the latest update. I hope this makes sense. I am sure it is easy for you guys. Thank you again for any help! Brian

I currently have a master spreadsheet (spreadsheet 1) containing 193 columns of property data relating to an improvement programme. The spreadsheet has a unique property reference number in column A. Column B onwards contains information about individual property elements with the year that each needs to be replaced and the number of years before each element needs to be replaced.

I have a second spreadsheet (spreadsheet 2) containing 203 columns. Not all of the columns in spreadsheet 2 are in the same order as in spreadsheet 1. Spreadsheet 2 also has a unique property reference number in column A. Column B onwards contains information about the individual property elements with an updated year of replacement and number of years before each element needs to be replaced. Not all of the property references in spreadsheet 1 will be in spreadsheet 2. Each spreadsheet only has a single instance of each unique property reference number.

I would like to update column B onwards in spreadsheet 1 with the updates from column B onwards in spreadsheet 2. Where there is no value in one of the cells in the columns in spreadsheet 2, I would like the existing value in the cell in spreadsheet 1 to remain.

A very simple example of the data set is as follows:

spreadsheet 1:-

Reference Year to replace Number of yrs to replacement
12345 2011 -1
23456 2012 1
34567 2011 -1
45678 2013 2
56789 2015 4
67890 2012 1

spreadsheet 2:-

Reference Year to replace Number of yrs to replacement
23456
67890 2013 2
12345 2021 10
34567 2031
56789
45678

Column A in spreadsheet 2 is not sorted ascending. In the above example, I would want to update the cells in spreadsheet 1 for the 'Year to replace' and 'Number of yrs to replacement' columns for property reference 67890 from '2012' and '1' to '2013' and '2'. For property reference 23456, I wouldn't want to update the existing values in spreadsheet 1 ('2012' and '1') because there are no updated values for this property reference in spreadsheet 2.

I hope I have explained things but let me know if any further clarification is needed.

I have a simple spreadsheet , which SHOULD copy some data from a UserForm (cmbCheese) and enter it into the active cell. I keep getting errors and I'm not sure what the mistake is I am making.

	VB:
	
 mcrInsertCheese() 
    Sheets("Sales List").Select 
    Range("C4").Select 
    ActiveCell.FormulaR1C1 = cmbCheese.Text 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The simple spreadsheet already has a macro that add a new row, copies up the formats, validation and formulas and increments the BatchNumber- I am missing something simple- what is it?

Any advice please.

Hi,

I am working on userform spreadsheet 11.0 control. I want to do on thing with this spreadsheet like i did in the normal excel worksheet, but i am not able to do it. i want to create a macro to undo between a range given. for example i do this in normal worksheet.

eg: Private Sub Worksheet_Change(ByVal Target As Range)If ActiveCell.Row 2 Then: Else Exit Sub
If ActiveSheet.Cells(2, ActiveCell.Column) ActiveSheet.Cells(1, 250) Then: Else Exit Sub
If Not Intersect(Target, Columns("E:P")) Is Nothing Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End Sub

Now, Here we have worksheet change event according to this if any change happens into the worksheet. this code will undo that change for that particular range then and there. Now i want to apply this similar code into spreadsheet control, but there is no spreadsheet change event is there. Please help me on this.

I am using Excel with my .Net Application. My Application supported only Excel XML SpreadSheet due to the reason for XMLObject. so I need to give sharing & Track Changes for my Excel XML SpreadSheet Application. Please help me

need a help, I use a macro to automatically translate TXT file, but my question is: sometimes my spreadsheet contains 10 lines, sometimes 50, some 1000 lines, how do I automate or even informing the implementation of the generation of txt composition of all lines: assuming that this spreadsheet has 500 rows I tell A1 to A500 without me having to be altering the macro.


	VB:
	
 Test_txt() 
     
     
    Dim nFile As String 
    Dim nSheet As String 
    Dim nExtension As String 
     
     
    Let nFile = "C:dirt.CI02" 
    Let nSheet = "CONCATENADO" 
    Let nExtension = "A1:A1900" 
     
     
    Sheets(nSheet).Select 
     
     
    For Each nOccurs In Range(nExtension) 
        Print #1, nOccurs 
    Next 
     
     
    Close #1 
     
     
    MsgBox "Exportado!", 
     
     
    Sheets(nSheet).Select 
End Sub 

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


How do I remove "Read-Only" status from Excel 2007 spreadsheet so that I can write on it. tugboat

Is there a macro to automatically save and close a spreadsheet after a designated time?
I am trying to eliminate the issue we have with a particular network file that is sometimes locked due to some user having it open every time.

I have a userform ("FTEInput") with a spreadsheet object titled "Spreadsheet1". I have it so that information input into the userform will load into ranges in the workbook upon closing the userform. I want it so that any changes made to the workbook will also be reflected when loading the userform again. I get an "Object required" error when attempting this code:


	VB:
	
 FTE_Input() 
     
    FTEInput.MultiPage1.Value = 1 
    With Spreadsheet1 
        For X = 1 To 10 
            .Range(.Cells(1, 4).Offset(X, 0), .Cells(1, 22).Offset(X, 0)).Value = Range("mCiv" & X).Value 
            .Cells(1, 2).Offset(X, 0).Value = Range("MCivRange").Cells(X, 1).Value 
            .Cells(1, 3).Offset(X, 0).Value = Range("MCivRate").Cells(X, 1).Value 
        Next X 
    End With 
    FTEInput.Show 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code that works to go in the opposite direction is:

	VB:
	
 10 
    Range("mCiv" & X).Value = Me.Spreadsheet1.Range(Me.Spreadsheet1.Cells(1, 4).Offset(X, 0), Me.Spreadsheet1.Cells(1,
22).Offset(X, 0)).Value 
    Range("MCivRange").Cells(X, 1).Value = Me.Spreadsheet1.Cells(1, 2).Offset(X, 0).Value 
    Range("MCivRate").Cells(X, 1).Value = Me.Spreadsheet1.Cells(1, 3).Offset(X, 0).Value 
Next X 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I think I'm just calling information incorrectly but I'm not sure how to fix it.

I have created an "alarm" spread sheet that sounds an audible alarm at set times, here's the code I used:

	VB:
	
 PlaySound Lib "winmm.dll" _ 
Alias "PlaySoundA" (ByVal lpszName As String, _ 
ByVal hModule As Long, ByVal dwFlags As Long) As Long 
Const SND_SYNC = &H0 
Const SND_ASYNC = &H1 
Const SND_FILENAME = &H20000 
Sub PlayWAV() 
    WAVFile = "C:UserstdurstMusicchime_big_ben.wav" 
    Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) 
End Sub 
Sub SetAlarms() 
    Application.Calculate 
    Dim myCell As Range 
    For Each myCell In Range("A2", Range("A65536").End(xlUp)) 
        Application.OnTime myCell.Value, "PlayWAV" 
    Next myCell 
End Sub 
Sub CancelAlarms() 
    Dim myCell As Range 
    For Each myCell In Range("A2", Range("A65536").End(xlUp)) 
        Application.OnTime myCell.Value, "PlayWAV", , False 
    Next myCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Basically the alarm time is set in A2 and down, this actually works very well. Where I'm having a hard time is with the "visual alarm". This sheet is to function as a time management aid for people who need to focus on certain things at very specific times, and those times change every day. The working environment has a lot of background noise so relaying solely on an audible alarm is not very wise.
What I'm trying to do is to have a pop-up message in conjunction with the audible alarm, and the message should contain the text entered into the cell next to where the alarm time is entered (i.e: A2 ='s alarm time, B2 ='s text/reason for alarm).

Any help at all would be appreciated.

I need toi know how I can remove the spaces in UPC code numbers in one column. They are on the spreadsheet as follows: 1 23456 34567 8. I need them to appear as follows: 123456345678, without the spaces. Is there an easy way to do this? I have thousands of UPC codes to do.

(Sorry, this is an accidental repost.)

Hello All!

I'm trying to add the data from two spreadsheets within a workbook. Normally this would be fairly easy, but the information (the column title to be exact) is added based on a vlookup formula in both spreadsheets.

Here's what I'm referring to:

Table 1/Spreadsheet entitled PC Recon:

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

50.00 #2

#3

Table 2/Spreadsheet entitled CC Log:

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

75.00 #2

#3

This is the formula used to create the title/header for column D in Tables 1 &2: =IF(COUNTIF($'Line Numbers'.A2:B322;D10);VLOOKUP(D10;$'Line Numbers'.A2:B322;2;0);0)

This is where the data needs to land:
BA BB BI
C: PRE-PRODUCTION

NBR DAYS RATE ESTIMATED
ACTUAL 101 Auto Rentals

$0
XXX
102 Air Fares:

$0

103 Per Diems:

$0

So basically, I need to add D3 (in this example) from Table 1 and Table 2 into BI2 while keeping in mind that the information is connected to a VLOOKUP formula and subject to change (i.e. D3 could be 102/Air Fares or something else - depending on the project).

Thank you!

DLM

Hello All!

I'm trying to add the data from two spreadsheets within a workbook. Normally this would be fairly easy, but the information (the column title to be exact) is added based on a vlookup formula in both spreadsheets.

Here's what I'm referring to:

Table 1/Spreadsheet entitled PC Recon:

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

50.00 #2

#3

Table 2/Spreadsheet entitled CC Log:

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

75.00 #2

#3

This is the formula used to create the title/header for column D in Tables 1 &2: =IF(COUNTIF($'Line Numbers'.A2:B322;D10);VLOOKUP(D10;$'Line Numbers'.A2:B322;2;0);0)

This is where the data needs to land:
BAC: PRE-PRODUCTION

NBR DAYS RATE ESTIMATED
ACTUAL 101 Auto Rentals

$0
XXX
102 Air Fares:

$0

103 Per Diems:

$0

So basically, I need to add D3 (in this example) from Table 1 and Table 2 into BI2 while keeping in mind that the information is connected to a VLOOKUP formula and subject to change (i.e. D3 could be 102/Air Fares or something else - depending on the project).

Thank you!

DLM

Hi All,

Sorry if I'm not explaining this well.

What i am trying to do is save my spreadsheet to a certain folder but i have 10 stores so instead of changing the macro every time i set up a new sheet for a different store i would like the store just to input the folder name them selves in a cell.

here is my macro so far that works but this is without it referring to a cell.

Sub Reset_Mac()

' Save Completed form

Sheets("CupCheck").Select
Dim WSName As String, CName As String, Directory As String, savename As String

WSName = "CupCheck"

CName = "B1"
SName = "H3"
WName = "M3"
EName = "D3"

Directory = "Macintosh HD:Paperwork:Dropbox:Paperwork - Sheffield Arcade:Completed Cup Checks:"

' When working with windows directory is ""
' When working with mac directory is ":"

savename = Sheets(WSName).Range(CName).Text
storename = Sheets(WSName).Range(SName).Text
weekending = Sheets(WSName).Range(WName).Text
weeknumber = Sheets(WSName).Range(EName).Text

If Directory = "" Then Directory = CurDir & ""

ActiveWorkbook.SaveAs Filename:=Directory & savename & " " & storename & " " & weeknumber & " " & weekending & ".xlsm"

End Sub

as it stands i just change the part "Paperwork - Sheffield Arcade" and I'm trying to get it so if cell a1 says Paperwork - Sheffield Galleries it will save it in that folder instead.

once again I'm very sorry i am a noob at this i do look for ours online when i get stuck but this one i can't get to work.

Thanks

Richard

Howdy!
What I'm trying to do is have a spreadsheet automatically add today's date in column one, then add today's gold price in column 2, then today's silver price in column 3. In separate sheets I have the gold and silver prices that update from online sources when I update the workbook. I would like to have the new sheet add a line and auto fill the new date and new gold and silver prices when I click on update. Basically I'm wanting to keep a record of gold & silver prices without actually having to think about it. Any help on this would be appreciated.

Greetings,

I am attempting to use an ADODB connection to a closed Excel spreadsheet. I have been able to sucessfully connect to the worksheet with the FROM statement, but when I add the necessary WHERE statement it fails.

The line below is the sql string that is the problem.

Sql = "select * FROM [LY_Data$b1:S745] & WHERE ((([Cat_Name])= " & CatFilter & "));"

Any help would be greatly appreciated.


	VB:
	
 RetrieveLYCatData() 
     '=================================================================================
     'Retrieve LY P2P5 Data from Excel Pivot Table based on filtered Category entry
     'Created 4-2-12, repurposed 5-30-12
     '=================================================================================
    Dim Cn As ADODB.Connection 
    Dim rst As ADODB.Recordset 
     
    Dim RowNum(13) As Integer 'input count as 1- number of RowNum
    Dim FieldNames(13) As String 'input count as 1- number of FieldNames
    Dim ColNums As String 
    Dim ColNum As Variant 
    Dim CatFilter As String 
    Dim ThisYear As Integer 
    Dim LastYear As Integer 
    Dim StrCon As String 
    Dim SourceFile As String ' need to dim this as variable and apply to data source= in connectionstring due to different
userpaths
    Dim Sql As String 
     
     'Category as entered in Category Plannng Tab
    CatFilter = Sheet5.Cells(3, 2).Value 
    Debug.Print CatFilter 
    SourceFile = Sheet1.Cells(1, 1).Value 
    Debug.Print SourceFile & "   " & CatFilter 
    ThisYear = Sheet1.Cells(29, 2).Value 
    LastYear = ThisYear - 1 
    StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile _ 
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" 
    Debug.Print StrCon 
     
    OldStatusBar = Application.DisplayStatusBar 
    Application.DisplayStatusBar = True 
    Application.StatusBar = False 
     
    Application.StatusBar = "Updating LY Data     " & ActiveWorkbook.Name & "      Please be patient" 
    Sheet5.Unprotect Password:="" 
    Application.Calculation = xlCalculationManual 
    Application.ScreenUpdating = False 
     
     'defines Rows in Excel to be paired with FieldNames from DB
    RowNum(0) = 9 'BOH_Retail
    RowNum(1) = 16 'BOH_Cost
    RowNum(2) = 23 'BOH_Qty
    RowNum(3) = 31 'Sales$
    RowNum(4) = 38 'Sales_Qty
    RowNum(5) = 57 'P2_MD$
    RowNum(6) = 65 'P5_MD$
    RowNum(7) = 69 'NetRcpt_Retail
    RowNum(8) = 73 'NetRcpt_Cost
    RowNum(9) = 77 'NetRcpt_Qty
    RowNum(10) = 85 'MUC$
    RowNum(11) = 113 'EOH_Retail
    RowNum(12) = 117 'EOH_Cost
    RowNum(13) = 124 'EOH_Qty
     
     'defines FieldNames from DB Rows to be paired with Excel Rows
    FieldNames(0) = "BOH_Retail" 
    FieldNames(1) = "BOH_Cost" 
    FieldNames(2) = "BOH_Qty" 
    FieldNames(3) = "Sales$" 
    FieldNames(4) = "Sales_Qty" 
    FieldNames(5) = "P2_MD$" 
    FieldNames(6) = "P5_MD$" 
    FieldNames(7) = "NetRcpt_Retail" 
    FieldNames(8) = "NetRcpt_Cost" 
    FieldNames(9) = "NetRcpt_Qty" 
    FieldNames(10) = "MUC$" 
    FieldNames(11) = "EOH_Retail" 
    FieldNames(12) = "EOH_Cost" 
    FieldNames(13) = "EOH_Qty" 
     
     'Note: Column 73 removed for 52 week fisical year
    ColNums =
"6,7,8,9,10,12,13,14,15,17,18,19,20,23,24,25,26,27,29,30,31,32,34,35,36,37,41,42,43,44,45,47,48,49,50,52,53,54,55,58,59,60,61,62,64,65,66,67,69,70,71,72"

     'splits ColNums String into separate column ID's
    ColNum = Split(ColNums, ",") 
     
    Set Cn = New ADODB.Connection 
    Cn.Provider = "Microsoft.Jet.OLEDB.4.0" 
    Cn.ConnectionString = StrCon 
    Cn.Mode = adModeRead 
    Cn.Open 
     
    Sql = "select * FROM [LY_Data$b1:S745] & WHERE ((([Cat_Name])= " & CatFilter & "));" 
    Debug.Print Sql 
     
     '''Create query from worksheet in Excel-Test String  (original attempt at string)
     ''    Sql = "select * FROM [LY_Data$b1:S745]" & _
     ''    "WHERE ((([Cat_Name]) = " & CatFilter & "));"
     ''    Debug.Print Sql
     
    Set rst = New ADODB.Recordset 
    Set rst = Cn.Execute(Sql) ' Note error here executing Sql
    Dim r As Integer 
    Dim c As Integer 
    Dim LastWeekNumber As Long 
     
    While Not rst.EOF 
        LastWeekNumber = rst("Max_Week") 'last week of recorded data from table
         '    Debug.Print LastWeekNumber
        For r = 0 To UBound(RowNum) 
            Dim WeekId As Long 
            For c = 0 To UBound(ColNum) 
                WeekId = (c + 1) 
                 
                If WeekId

I have one spreadsheet with all of my data, as follows:

Column 1 Column 2 Column 3
Horse a z
Horse b y
Cow c x
Cow d w
Pig e v
Horse g u
Pig h tFrom another tab on the spreadsheet, without using auto filter (which would be easiest, but won't work as I need static data), how can I pull all rows/columns and data for only data that matches a certain criteria in column 1? Example output:

Horse a z
Horse b y
Horse g u


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