I am trying to analyse, in a set of data that how many followup made by a salesman between a particular set of dates. I've attached a sample. In this example I differentiate between people if the name and age are same. I know that it is possible with formula 'countifs' but how?
View 3 RepliesThe following code works very well up to a point. See previous thread Automatic Report Generation.
As I use it to change my yearly summary sheet all at once. However as it stands I need to insert a seperate line of code for each month and cell that I need data from I will have over 2000 seperate lines when I am done. Also if the file does not exsist yet then the cell returns a #REF (error 2023). And none of the other formulas in spread sheet will work. Is there a way to have the cell return a zero if the file has not been created yet?
Is there a way to try and automate more of the code below? Cell references will be all over the place. All the months for the year will be needed.
Salesman
Sales Amount
Introduced by
sales
introduced
sales
indirectly introduced
sales
Tom
300
none
Tom
300
[Code] ........
I have salespeople who I want to pay a bonus to but I've also offered them a bonus on how much the salesmen they referred to the company sell and this goes like a pyramid down 3 levels.
so I have a list of salesmen next to there name is how much they have sold then who introduced them. from this I need to make a list of each salesman and below all the people he introduced and the people they introduced with the sales in the cell next to them.
To show how it could look I've done an example above of the information I need for tom, as you can see all the data is in the first 3 columns and tom needs to show that he introduced bob & Sue, but because Bob & Sue also introduced someone, tom needs to show he was involved in this as well and jo, terry, mark need to show, preferably in a different column as the bonus for them is lower.
I have a master spreadsheet being updated by several people, how can I/we, get a notification set up for when ever someone updates the master spreadsheet.....
View 1 Replies View RelatedI have made some changes to a workbook and now every time I want to close it I'm asked if I want to save the changes, even when I have made no changes. If I click 'Yes' to save changes the problem still occurs the next time it's opened & closed.
I have checked external connections and there are none. I have done a 'find' to check that there are no formulas looking at another sheet, which I cannot find.
This document has to have a live calculation function - I have read some suggestions around turning off auto calculations but this isn't a possibility and I'm not sure that this is where the problem lies.
The previous version does not have this problem, so it is something I have done between v2.2 and v2.3. I have attached both for reference. Note that they were originally xltm's not xlsx's - I could not upload macro-enabled templates, but the same problem occurs in v2.3 in this format.
I have an excel spreadsheet that when opened and closed prompts to save changes, even when no changes have been made.
The spreadsheet is a copy of another spreadsheet which has then been modified. The original spreadsheet doesn't have this quirk.
There are no 'links' in the spreadsheets. Which was my first thought.
Is it possible to write a macro that will run if a user enters data into any cell in the worksheet that has a formula in it.
View 9 Replies View RelatedI am using Excel 2003, with Windows XP. I am monkeying around with a form to allow my boss to randomly assign employees to a daily task. There is a list of 15 names, but I'm just using the numbers 1 through 15 until I get the hang of this formula.
I am using MRAND, and the formula is working perfectly when I specify a set # of values to deliver. I wanted to see if I could allow him to enter the # of employees he wants to task in Cell A1 so that the number of random employees that are assigned varies with his selection in A1.
In other words, one day he could specify he wants 3 randomly assigned names to work on the task and the next day he could randomly assign 6 names/people, etc...
I thought I could just plug $A$1 in "maximum" in the formula below:
{=MRAND(maximum,1,15)}
But that's giving me a #NUM! error.
I need to step out of a macro based on a user respone, and I don't know how to end this macro if the user needs to correct a cell entry.
If they respond "no", I want to sub to end and select the cell where the correction needs to be made.
I've tried for a while on my own but no luck. I'm looking to delete cells D21 and 22 when any changes are made to the sheet.
I've tried using Private Sub Worksheet_Change(ByVal Target As Range) but no luck.
The workbook I have has a tab for Master List of Transactions - there is data in columns A through J. In column E, when a year is entered, the data in columns A through D is copied to a tab for the specific year entered in column E. When the data from columns A through D is copied to the specific year's tab, it does not change anymore. If the information is changed at all on the Master tab, the macro does not currently work to automatically update the information on the specific year's tab but I would like it to. Is this possible?
Here is the macro I currently have:
Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Application.ScreenUpdating = False
Dim rng As Range
Dim row_copy As Integer
[Code] ......
How to automatically update the year tabs for changes on the Master List of Transactions tab.
I have created the following in an effort to automatically goal seek whenever changes are made to the cell C2.
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
As far as I know, the Macro is running all of the time? When I use goal seek manually it works fine. Assuming the above is running, when I change the value in C2 nothing happens. Does the fact that I have a three sheet workbook make any difference? I am sort of lost. By changing the value in C2 I want Excel to change the value in D19 to match C2 by adjusting D3.
Application.EnableEvents = False
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
Application.EnableEvents = True
Doesn't seem to work either. However, I don't really know if other settings in the workbook are correct.
Is there a way of eliminating un-used rows & columns to bring your file size down.
I already know about eliminating unnecessary pages, and I've cleaned up defunct modules, etc., and anything else that's just clutter.
The Row & Column thing would be a big help.
If nothing is typed in and the OK button is pressed, it changes the entire sheet to values. I'd like the macro to stop if nothing is selected.
Dim sCol As String
sCol = InputBox("Select Column")
Set UserRange = Range(sCol & "9:" & sCol & "35")
UserRange.Value = UserRange.Value
How do I select a shape I have just created with BuildFreeform function? It is not convenient for me to reference the resultant shape as I have done below:
Public Sub DrawDoor(TileType As String, Xpos As Integer, Ypos As Integer)
With ActiveSheet.Shapes.BuildFreeform(msoEditingCorner, Xpos * 9 + 1, Ypos * 9 + 5)
.AddNodes msoSegmentCurve, msoEditingAuto, Xpos * 9 + 5, Ypos * 9 + 1
.AddNodes msoSegmentCurve, msoEditingAuto, Xpos * 9 + 9, Ypos * 9 + 5
.AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 9, Ypos * 9 + 10
.AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 1, Ypos * 9 + 10
.AddNodes msoSegmentLine, msoEditingAuto, Xpos * 9 + 1, Ypos * 9 + 5
.ConvertToShape
End With
Shapes(8).Fill.ForeColor.RGB = RGB(200, 90, 20)
End Sub
I want to give the shape a name, without having to use Shapes(8) at any stage. Is this possible when making freeform shapes?
The first column is "Person Name" and the second column is date in format "6/27/2013". I want a formula to calculate the total number of calls a person made in the month of June.
View 2 Replies View Relatedi bought a tattslotto scratch card (for those of ya outside of australia, not sure what youd call em, basically lottery ticket/game), this game is essentially a crossword puzzle, you scratch your letters out in a box then you have a list of words, you need to find words that are made up entirely of your letters, the more words you find, the more you win ive kind of screwed up the card and scratched out words i dont have, however i do have atleast 2 words and i think ive got more, potentially could win big but wanna know how much before i go claim it
ive opened up a new workbook, in the A column ive got a list of all my words, then in C1 ive got a field where ive entered all my letters, wondering if theres a way to get excel to work it out for me (like an IF statement on the fields in column A to compare them to C1, or some kind of conditional formatting rule)
I made a command button on the worksheet from the Tool box. This is not made on a form. When I click on the button I would like to get focus set to a certain cell and made active on the same page. Like cell H2 for example.
View 2 Replies View RelatedHow can I search a range of cells that have a date entered by hand for a certain month and then sum the amounts given in another range of cells. For example, search several worksheets for payments made in September and then return the sum of those payments.
View 3 Replies View RelatedI have a master file which users are able to open via read only or go in via password to edit.
I'm not too bothered on the read only users more on person's entering the file via password, making any changes and then saving those changes.
Is there a way to trace what changes they made and would their name be saved as the new "author" of the file?
Scenario: I have 5 different tables, all having the same headers. The values under are Vlookups.
Objective: Create a dropbox that lists (Table 1, Table 2, etc.) For whichever table is picked, is the one that will show.
For example, if I want table 1 to populate, I would chose table one in the drop box and so on for the rest of the other tables.
I've tried to do conditional formatting for each of the "Discipline" values in this spreadsheet but it is restricted to only 3. Is there a way around this? I'm trying to setup a color code for the "Discipline" values for example RN=Red, LPN=Blue, Social Worker=Orange, Chaplain=Purple, STNA=Yellow, Volunteer=Pink & Other=Green. So when the "Discipline" cell/cells on each of the patient sheets (ie. "Doe, Jane", "Doe, John", etc.) is equal to say RN the cell would then turn Red either on the spreadsheet or only when sheet is printed.
View 5 Replies View Relatedhow to write a custom made IFERROR function which works on Excel 2003 that mimics as closely as possible the Excel 2007 IFERROR function without using any array formula.
View 2 Replies View RelatedIs it possible to detect when a auto-filter selection has been made ? Is that when an autofilter hides rows, the activecell could become hidden, which potentially may cause errant data entry, not knowing what cell is receiving entry as it is hidden from view.
if the worksheet contains a change event that autosizes the target row, it unhides the filtered hidden row in the process. I could say if target is hidden ignore autosize, but i would prefer to activate a visible cell (first visible cell for filtered column) on selection of autofilter.
The intent of the code below is to run the marco HideCol when a change is made in cell C10. If I actually change the value in C10 it works. However, I have C10 linked to another worksheet (=anotherpage!A13). When the value changes, my macro is not executing.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range
Set changed = Range("C10")
If Not Intersect(Target, changed) Is Nothing Then
HideCol
End If
End Sub
I have been working on this data entry and validation tool for work for a while now. I've got most of it down and works just as I want it to. However, I have made some validation lists for people to select and animal ID when entering some data. Once the animal ID is entered I want to remove it from the source data and the list to stop people entering a duplicate number for the same event.
I'm not sure how easy/difficult this will be but the whole reason for this project was to stop people entering the same data and only allow them to enter data that I pre-define. Its to cut down on mistakes where somone will enter the wrong ID and then finding the correct ID becomes difficult due to having over 4000 animals on site.
Count number of occurrencesI have a large spreadsheet that list customer and call dates. Each call is displayed in a separate row with the corresponding customer next to it - so each customer may show up multiple times with different call times. I want an equation that will count if 4 calls (occurrences) have been made to each customer and if it is the answer is "1". I have been trying to make it working with COUNTIF but not getting the results.
View 4 Replies View RelatedI am using the...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
... to capture when a user is about to close a workbook. I then give them a message: "Are you sure you want to exit? Changes you made since you last saved will be lost."
However, to make it neater, is there a way to only trigger the macro if they have made changes since the last save? In other words, if they have not made any changes since the last save, the message will not be displayed, since it is irrelevant.
I've developed a workbook that is designed to allow users to produce a list of aspects (one per worksheet) and edit them as the aspect changes. However, i would very much like to create a 'log' of changes. For example, everytime a cell is changed the cell reference, worksheet reference and date are added to a list on a hidden worksheet.
View 9 Replies View RelatedI am building a template in Excel and I want an error message (or any message) to appear to the user if they select from a static drop-down the same option in column I as in column J (i.e. they have to pick something different in each case).
Issue is I can't exclude any options from the second validation because, essentially, it's the same list. So if, on one row, a person picks "Bill" in column I, they can't pick "Bill" in column J. However, if they have picked "James" in column I, they can pick "Bill" in column J.
Don't know if this is well explained enough but would appreciate any tips on this - I've tried messing about with validation, conditional formatting, putting an IF statement in the next column and so forth but I haven't found a method that isn't convaluted and easy for the user of the template...