My Worksheet uses Hyperlinks to open files. I want to have another cell in this Worksheet incremently report the number of times that the Hyperlink is used to open a file, like a Web Style "Hit" Counter.
View 4 RepliesDoes anyone know if/ how i might unlock hyperlinks on my protected spreadsheet without having to unlock the cells they are placed in?
View 3 Replies View RelatedIs there a way of counting mouse clicks on an active XL cell and displaying the real time count in the cell, in essence giving a click count? Or in the case of a handheld, tapping an active cell and having the value incrementally increase with each tap...
View 8 Replies View RelatedI'm dealing with an Excel spreadsheet. I have a list of orders on one sheet and I need to keep a running total of the amounts of the items being ordered.
Example - I have three different orders:
sheet1
1. Fried Chicken
French Fries
2. Salad
French Fries
3. Fried Chicken
Salad
French Fries
sheet2
Fried Chicken 2
French Fries 3
Salad 2
So, on sheet2 I need to somehow reference sheet1, find how many times the items "Fried Chicken", "French Fries" and "Salad" are listed and then put the total amounts next to the name on sheet2. But if I add any more of any item to the sheet1 the total will go up on sheet2.
Does that make any sense? I tried to say it as simply as I could.
I'm not really dealing with fried chicken, salad and french fries here, but that's a simple example to show you what I need.
I have a spreadsheet that has numbers in column A (example = 31, 15, 159) I want to count the number (159) each time it occurs and keep a running count of it in say column B... example..... A1 = 159, A2 = 31, A3 = 159, so B3 would show (2) as the number 159 has appeared 2 times thus far.
View 3 Replies View RelatedIs there any way to create a running count in one horizontal thread? I can create a running count in vertical columns,but not horizontal threads?
I have a spreadsheet that I want to keep track of how many rounds mulitple weapons have fired. I have a current rounds cell, a last fired cell and total rounds cell. The problem is that when I enter the number of rounds shot in the "last fired cell", the sum of the total rounds cell goes up (via the sum function) however, is there a way to keep that number there, even though I will replace the last fired cell with a new number the next time I fire the weapon?
I am trying to get my Excel Worksheet to count the times that a cell changes. The data is in Column C, and I want to enter it in column H. For example. If Currently, the count in C1 is 5, but changes to 4, H1 should read one, then the next day, if I change the value from 4 to 7, H1 should read 2, and so forth and so forth. I am counting the amount of changes to a cell.
I want it going the length of the column, but for each cell, like C2 lines up with H2 and so forth. I have looked everywhere for a particular Macro, but am having a hard time finding one.
Have list of employees and their dependents organized into the following columns:
Employee ID (unique to employee), Relationship Code (E=employee, S=Spouse, D=child), andBirthDate. Would like to add a column called Sequence Code which counts the family members (employee= 0, S=1, oldest D=2, next oldest D = 3, and so on).
Employee ID RelationshipCode BirthDate Sequence Code
100006641 E 2/5/75 0
100006641 S 1/2/75 1
100006641 D 3/9/89 2
100005555 E 2/5/77 0
100005555 D 1/2/89 2
100005555 D 3/9/88 1
Note that the list is sorted by Employee ID, but not Birthdate. Can this be done with a formula in the Sequence Code column? I really suck at VBA
You insert a yes or no click buttons into your excel document. There are some many yes button's clicked and so many no buttons clicked. Can you get excel to determine how many yes and no buttons are clicked and give a percentage?
View 9 Replies View RelatedI have 1 button that opens 1 workbook within a sheet. How can I give a person that clicks 1 button a choice between 3 macros? So I have 3 macros in a sheet that open different workbooks. The problem is I don't have room to make 3 buttons... so I was thinking if there is a way to press 1 button and get 3 options, that would be cool! Also, I don't think someone would identify the document by the name of the macro because I have a foul way of naming my mac's. Click one button and see a message like "Would you like to open Button1 Button2 Button3" something like this!
View 3 Replies View RelatedI have a workbook with a bunch of VB code in it.
I have this workbook and any other workbook open, but this workbook is not active. I click the X to shut down Excel. It calls the Workbook_BeforeClose event, then asks if I want to save my work (as it should). I select "Save All" and the shut down is aborted.
It never makes it to the Workbook_BeforeSave event.
I then click the X a second time, and I'm not prompted to save but the shut down proceeds with saving all and then closing Excel.
==> Why does it require a second click of the X?
If this workbook is active, all works fine.
I've commented out all event code. Didn't help.
I've deleted my commandbar menu before attempting to close.
The order in which the workbooks were opened doesn't matter.
What might I be doing in VB to cause this behavior?
How do you add commands to a right mouse click?
I click on a cell, do a right mouse click and would like to be able to select a command that puts the current date and/or time in the selected cell.
The code changes the cell color depending on how many clicks you give it.
The code it self works fantastic but the problem i have is if i manually unlock the sheet every time i click any where in sheet it locks it again.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Error
Dim I As Integer, I1 As Integer, ws1 As Worksheet
Set ws1 = Worksheets("36hr Plan")
ws1.Unprotect "muppeticity"
I1 = 40
For I = 5 To 25 Step 5
For I1 = 22 To 204 Step 13
Offset1 = 0
If Not Intersect(Target, ws1.Cells(I1, I)) Is Nothing _
Or Not Intersect(Target, ws1.Cells(I1 + 1, 5)) Is Nothing _
Or Not Intersect(Target, ws1.Cells(I1 + 2, 5)) Is Nothing Then.............
I have a sheet that pulls data from an external source. I want it so that when a person clicks on a cell or a range of cells, it automatically locks and hides the formula. I don't want to lock it by using the conventional method because protecting it will cause the external data not to be able to populate the cells. So my thinking is that when the data is imported, and the user clicks on a cell, it will automatically lock and hide.
I need columns
A C E:BB to be locked and hidden as soon as someone clicks it. Is this possible?
I read somewhere that I may be able to do some type of "Private Sub Workbook_SheetSelectionChange" to achieve this.
is there any way a macro can be automatically run when a user clicks unto a cell?
View 5 Replies View RelatedI have a user form that has 12 check boxes corresponding to the months on the year. All or none of the check boxes can be ticked at the same time. How can I get the value of the check boxs to one cell on the worksheet.
ie. Check boxes 'Jan', 'Feb', 'June' and 'Oct' are ticked. On the worksheet, cell A1 would say 'Months chosen: Jan, Feb, June, Oct'
writing the correct command to auto popup a calendar when a user doubleclicks in any cell in a range of cells. I have the code for individual cells but would like to apply to a very large range of cells and do not know what the correct command is.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$C$160", "$C$161", "$C$162", "$C$163"
Cancel = True
Call OpenCalendar
End Select
End Sub
I have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.
View 2 Replies View RelatedI need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.
Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.
Examples of the contents of cells in col D with the "Category:" prefix are as follows:
Category: Adversity
Category: Answers
Category: Assurance
Category: Blessings
Category: Build
Category: Change
Category: Children
Category: Choices
Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.
I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.
I want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
View 6 Replies View RelatedColumn A has current building, column b has future building. Would like to count the number of changes without adding a separate column with an if statement.
View 3 Replies View RelatedI am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
I want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.
The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.
I want to count cells in column AA that are graeter than 160, and in column N = "RM" and in column A = "CBP". Can't seem to get this right.
View 4 Replies View RelatedVBA which would count data in Column F of dump Sheet and paste the count in master sheet B2 Cell.
View 7 Replies View RelatedI require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.
For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.
The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?
Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80
Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83