The first tab I have a summary sheet which I want to auto populate with a sumproduct function, the second tab is where the dates and values are I wish to sum the value if it’s returned in January. I had a go to try sum the entire range see below but it’s not working
The ultimate goal I am trying to achieve is if there is a date in January in the date returned column and the php column does not contain a value I want it to sum in the summary sheet in January. Hopefully if anyone can advise that would be great then I can look at the formula and try workout to sum only if there is a value in the php column in January
I need the tabs of a project action log to auto-populate and auto-delete in a master log. (So when something is added or deleted in a tab it is added or deleted on the master) I use excel a little bit for work and personal finance purposes but I have zero experience with macros or VBA.
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
I have 5 columns: A, B, C, D & E of which I have drop downs for: Yes, No, Maybe, Not Sure for A, B, C, & D. I need column E to offer a hyperlinked mailto:firstname.lastname@example.org when A, B, C have Yes, Maybe or Not Sure selected. If Yes, Maybe or Not Sure is selected for D, I need to have a hyperlink mailto:email@example.com. When no is selected for any of them, I need to have it say None.
My macro runs, and it inserts a cell, but in order to record data on a summary page, I need a formula to auto populate, and I've tried having the macro copy/paste and I've tried ActiveCell.Formula...and nothing seems to be working. The formula needs to go into Cell B3.
I have a database in the form of a spreadsheet with the following column headings: A=DATE (3 letter abbreviation for the month...jan, feb, mar, etc) B=Client C=City D=State E=Department F=Contract number
Is there a formula that I can enter in another worksheet (which is being used as a report), that will "grab" the monthly data for all entries for a given month (i.e. "mar"), and auto-populate corresponding cells with each?
I am trying to add some code that will auto populate cells based on entries and list selections. In the attached spreadsheet, when information is added to cells in column 'A', the date should auto populate the cells in column 'C'. When a status of "Complete" is selected from the dropdown box in cell 'B', the date should auto populate in the cell in column "D".
The code partially works in that it will auto populate columns "C" and "D", but the code errors out every time and I have to cancel the error to continue to the next entry.
Based on selection in column C populated using a drop down list from a range titled PracticeID. Other named ranges, all on sheet titled Validation, are PracticeName and Name_ID (a 2 column range made up of PracticeName and PracticeID). This works great the first time a selection is made in column C. But, of course, if the user needs to change the selection in column C, the value in column D does not change. Would the cell change event be the appropriate place to place this action?
I use the code below to autocreate a folder and hyperlink to it. It creates a folder when I enter a drivers name in column D it then turns the name into a hyper link to that folder - my problem is I have 2 files in the drivers folder on the C drive - one called wkd.xlsm (a small spread sheet) and one called notes.txt (a notepad document) I would like these files to be automatically added to the folder that is created when I add a drivers name to the D column - I would also like a hyperlink to be created to the wkd file in column H and a hyperlink to the notes file in column X.
Private Sub Worksheet_Change(ByVal Target As Range) folderpath = "c:drivers"
If Not Intersect(Range("D3:D1000"), Target) Is Nothing Then If Target.Value "" Then If Dir(folderpath & Cells(Target.Row, "D") & " " & Cells(Target.Row, "C"), vbDirectory) = vbNullString Then
I have my projection formulas that use the date and age to estimate production on each farm. Basically the age is calculated first and then the production rate is estimated. I have been trying to figure out if, instead of having a formula in each cell, can I use VBA to populate the ages and then the production.
Here is an example:
FarmAge8/22/09Age8/29/09Age9/5/09Age9/12/09Age9/19/09Age9/26/09Age10/3/09Farm 1 34181351813617937179381793917940177Farm 2 39223402204122042220432204422045218 All the functions I have created work, I just can't have the worksheet auto-calculate due to the total number of cells with functions. Calculating the worksheet takes approx 1 min...
I should be able to adjust any code to the ranges needed, but this example shows the basic layout.
Some Background info: This is my first post! I am a total novice when it comes to VBA in Excel (but I'm a fast learner!), so please bear with me if my question is either obvious or easy (or if this is a question that's been asked 14980213 times already). I have some programming experience, but all in C or C++, not in VBA--this makes the project challenging. I also have to hand this off in a few months and trust that it'll never break, ever--more challenging.
I'm trying to make a Participant Tracking System for some workshops we offer. The intent is to make a userform so that an administrator can input all the information for the participant in question. One important question on the form is which course the participant intends to take; the snag is that the current list may change over time.
The Spreadsheet is set up with Course headings starting at R8C8, and continuing across for all 16 courses we currently offer. The data for the participant is entered into Rows 1-7, and the date they completed the course in the appropriate column for that course. There are some formulas in Rows 1-7, the important one here counts up the number of classes currently offered.
So, below is the code i'm trying to use, at least to start. I am making a combobox that has all of the available classes.
Private Sub UserForm_Initialize()
Dim ClassList() As String
'R5C6 contains a formula that calculates the number of classes offered. The value is currently 16 Redim ClassList(R5C6)
iCount = 0 y = 8
Do While iCount > R5C6 'R8C8 is where the class list begins. 'It continues horizontally along the rows for the 16 titles offered. Range(R8Cy).Select ClassList(iCount) = ActiveCell y = y + 1 iCount = iCount + 1 Loop
With ClassListBox .Clear .List = ClassList .ListIndex = -1 End With
I used to have a line that replaced the following chunk.
Range(R8Cy).Select ClassList(iCount) = ActiveCell
ClassList(iCount) = R8Cy
and did nothing for me at all.
When I initialize the userform, I get a blank combobox. It doesn't do what I want it to do, but that's probably because I'm telling it to do something weird.
I'm trying to figure out a way to populate multiple cells automatically with certain information after entering info into a single cell on a separate worksheet in a separate workbook.
Our "master" sheet has 22 total columns (A-V), and our weekly "status" sheet only has 7 total columns (A, E, P, L, M, N, R).
Column A on both sheets is labeled "Sample Number". On the "status" sheet I want to be able to type, for instance, "2012-228" into A2 and have the corresponding values from Columns E, P, L, M, N, R on the "master" sheet automatically populate into B2-G2 on the status sheet if this is at all possible.
I am trying to auto populate into a column that starts in cell E5 and references cell C5 (along with a dataset in N and O). In cell E6, I copied and pasted this formula and changed all of the C5 values to C6. When trying to use the auto populate cross hair tool, excel changes all N and O cells, from O165 to O166, O167 when they should have remained O165 and N165 for every cell.
Every time a name is selected in column C12:C15, I need that respective row to populate with $10 in column J. When column C12:C15 is blank, I need that respective row to read $0 in cloumn J. It's either one (10) or the other (0).
There are rows above and below it as well that have nothing to do with this, so the formula/function would be specific to just this subset of rows (rows 12-15).
I get close, but can't seem to nail this simple formula/function down. I am unable to download any sheets due to my work's firewall.
The attached workbook requires users to enter a date on the SAT sheet (B3). I would like that date to always represent Saturday (the first day of our work week) Is there a formula I can use to validate if the date entered by the user corrleates to Saturday or is there a way to auto-populate the workbook with the Saturday's date?
I have this code inside a userbox to auto populate a textbox on the same form, the combibox info is located on row E, and it populates the textbox with info off row G, but how can I change this to pull the info off row C instead of G???
Code: Private Sub ComboBox1_Change() var1 = WorksheetFunction.VLookup(ComboBox1.Value, Worksheets("Basic").Range("E11:G90"), 3, False)
I have two tables on two different worksheets. I would like to automatically populate a distinct list into column A in table 2 from the data in column A in table 1.
Table 2 must auto populate from table 2 every time a new row is added into table 1 - without any extra user interaction e.g. pressing a button etc. No offense intended to my better half but if she has to do anything more than enter a new row in table 1 she'll likely manage to screw it up after a week
I kind of managed to get it working using the below formula but both sets of data must be in tables so I can't copy the formula down the column without creating redundant table rows.
I need to auto populate an excel spreadsheet however it has 3 criteria to match. Pressure, size and name. I need to somehow lookup the name and if it matches the pressure rating and size then return the quantity in that cell. There is 2000 cells and I am sure there is a logic function or someway to complete this task faster then manual entry.
I am looking to auto generate a roster/schedule. I have a spreadsheet with shifts for each weekday that I need to populate every week with names to pick from a drop down list. the drop down list is made using data validation and looks at a dynamic named range to allow me to select only the people who are available to work a certain shift.
I have 3 dropdown boxes I created using the control toolbox which pull corresponding information from 3 different columns. I would like to be able to format them so that upon data entry in the first dropdown box, the subsequent two boxes auto populate with the corresponding data. Currently I have to select the information for each dropdown box manually.