I have included a copy of the spreadsheet. What I need to do is get a cummulative duration report for each crew. This particular spreadsheet has less data than most.
Many times there are 8+ crews. I need to make a column or graph or report or something that will continually update as changes are made and report back something like; Sewer 142 hours, Water 200 hours etc...
I would like to create one page (sort of like a form) to use to collect specific weekly payroll related data for multiple employees and be able to automatically transfer that data to its own weekly data page based on the week date, and then also automatically post specific data to each individual employees' total pages. While I am self taught and comfortable with formulas I do not yet have a lot of experience with VBA and am just now recognizing and exploring the true power of Excel so I don't always yet see the best way to accomplish my goal.
My current way of doing this is to reuse the same form page. I plug in the data for each employee that week, it calculates, I print it out, and then I manually update each employee's total page for that date. As I do this I lose the previous week's details. I would like to retain a page for each week's details in addition to having each employee's total pages. Is this possible or is there a better way to accomplish my task?
I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
I have a small sample table that replicates what I am trying to do
A
B
C
D
E
1
Bob
Jim
Mary
Sue
[Code] ........
I would like to have excel put in compile the list of members on Teams 1 through 4. So in A7 through A10, I will put the teams and then in the B column I want the compiled list seperated by commas for each team so that they read like this...
Team 1: Bob, Jim, Mary, Sue
Team 2: Bob, Jim, Sue
Team 3: Bob, Mary
Team 4: Bob, Sue.
Of course this should be dynamic so that if I place an x in C4, then Team 3 would read as Team 3: Bob, Jim, Mary.
I have tried various index match offset combinations and did not have any success.
have a look at the attachment. I need to generate a report based on Data entry in one sheet and summary of reports to be automatically generated in the sheet named MIS, there are many conditions for those columns in the MIS sheets, as of now I need to do all that manually using Subtotals and other tool.
View 8 Replies View RelatedI have this concept of a table of responses that have to be generated every month or on command. These reports export from a website into a certain format, its easy for me to adjust it so it can populate a the table. Although, i want to incorparate my date picker so everytime i pick a certain date a diffrent set of data will populate from the report. it seems very possible, just need to know where to go from. Either a VBA code or a fomula to allow for this to happen.
Heres and example..(cell linked to date picker)........column1 column2 column3,(date picker from microsoft),,each has a corresponding month
.....................................................................jan
.....................................................................feb
.....................................................................etc.
say i pick january, i want these columns to give me my data from a sheet or other workbook(the january report). i want everything linked so it will do this for virtually every month of the year. ive attached an example, the only thing i can think of is to make the cell that changes with when i pick a date to link to the report criteria.
I have been working on a project with 30 products and 20 different companies.
I am working on a business case, where there are launch dates for various companies with expected sales, contribution margin, NPV etc.
The idea is that I can create specific scenarios (What is Company1 doesn't launch the products, etc), and there is an accompanying each company. Because it can be difficult to see what products are included in the current calculations, I would like to use some kind of function that produces a list that matches my criteria. (In this case, all cells for a company that are larger than 0).
I would like to find a function that finds all products that have a launch date and create a list of them -- without spaces between non-matched results. And that it updates whenever data is changed (new launch year or new product in company's profile)
I have included a generic excel file to show what I am going for.
I have 2 columns of data in one sheet that list dates (say Master Dates) and transactions (say Master Transactions).
In another sheet i would like a formula that can choose the correct transactions that fall between certain dates (call Date Beginning and Date End) and generate a list.
i have tried this array:
If((Master Dates>=Date Beginning)*(Master Dates<Date End),Master Transactions,"")
Problem with this is when used in different columns for different date ranges there are large gaps in the generated list...
If a user inputs "Dispatch" into any cell on column G, I need to generate a Msgbox pop up.
View 3 Replies View Relatedfind the attached, I've some formulas on cells m,n & o and they related to each other, i just need n & o should be zero if m is zero.but the same formulas
should exist also.
I'm trying to generate a property's name based on an address. I thought the index/match functions would best serve my purpose, but here's my problem.
The address I'm using to search may not exactly match the table I'm looking in. For instance:
The address I'm using to search is 456 E. Bell Rd.
The address in the table is 456 EAST Bell Rd.
How to generate a list base on the differences of another list. let me explain; I have a list of combination A1:F2200 each row of course the numbers have difference, example:
589162045620273342451261419316121721264271837414551
As you see the difference in the first row for this small example is 317425 and the list base on this difference is :
145121641256131742367141843478151944589162045691017214671011182247811
1219234891213202449101314212550111415222651121516232752131617242853
The maximum number is 53. doing this one time find, but I have to do it with my 2200 difference.
I have an excel sheet with over 50thousand entries, each entry is identified by an ID code in the L column. I want a code that creates a new workbook with all entries having thesame ID code. if it's not too much, i want to have a pop-up window that asks me to enter the ID code.
View 9 Replies View RelatedI'm a graphic designer and i'm trying to build a spreadsheet that will help me in doing my accounts.
Ok, so i'm trying to set up a function that will generate a price based on three fields:
Lamination:YES or NO
Duplex: YES or NO
Stock: Around 14 different stock options.
I had it working using a nested IF but I ran out of the 7.
I then tried using CONCATENATE using "&" to seperate but excel said the formula was too long.
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.
View 9 Replies View RelatedI would like to generate a dynamic list of employees on sheets based on information on Sheet A. Here is my workbook structure:
-Sheet A contains all employees information (Name, division, salary hire date, etc)
-Sheet B is named Div A
-Sheet C is named Div B
-Etc...
Here is what I want to happen:
-Sheets B should pull the employees name and salary from sheet A only for employees of division A
-Sheet C should pull the employees name and salary from sheet A only for employees of division B
-Etc..
Sheet B, C , etc. should update the info and resort every time sheet A is updated.
I currently have a large spreadsheet that multiple people fill out. Each person fills out all the information in a row. At the end of the row, I would like a button that says "Generate Form" so that when clicked, a new sheet automatically opens with a template form that I created and is already filled out with the information that was just inputted into the spreadsheet. Also, there are four different template forms that could generate. For example, there are forms A, B, C and D. If the user inputs "B" into the first column of the row, then when he goes to click "Generate Form", a new sheet is created with all the information filled out in Form B.
View 3 Replies View RelatedI need to enter a revision string into a file.
Basically, I need to combine the filename and its generation time and then encode it into a string (i.e. a 6 digits HEX code or 6 digits using [0-9][a-z][A-Z]) so it would generate a pretty much unique revision number.
So, is there any function that you can think of that would do something similar?
If not, what is the best to tackle this?
Generate a CRC or md5sum?
What would be in your opinion a quick and efficient way to solve this tricky one?
I have a workbook that has 30 tabs in it. Each tab is a report card for students. What I'd like to do is create another tab with a button on it that when I hit the button it will search through each tabs range of D12:D40, D48:D76, D84:D112, D120:D136, J12:J40, J48:J76, J84:J112, and J120:J136. And if any of these cells has an MS in them then this new sheet I have created will list each students name which is in cell E5 and list what they recieved the MS for. This will be in the same row number but in column B. So if cell D12 has an MS in it then this report will list the students name and what's in cell B12.
Windows XP
Excel 2003
I have built conditional formatting into some cells on ' Sheet 1' and would like a message box (saying "Check errors before closing") to appear when the user tries to close the workbook if any of these cells are coloured red (ColourIndex 3).
View 3 Replies View RelatedI have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
View 5 Replies View RelatedI have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".
Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
column A = Date : 01/02, 07/02, 14/02, 21/02, 28/02 (x-axis : shows when the table is updated)
column B = project: x, x, x, x, ,x (name of the project and trend line)
column C = Delivery Date : 01/05/2009, 08/05/2009, 20/05/2009, 30/05/2009, 28/02/2009 (plotted on the graph)
column D = Status : Green, Amber, Red, Green, Blue (status of the project. the points should be the same colour as is described in the table)
column E = Comments: original, delay, supply, out of money, on track, delivered-wow! (these comments will pop up if the user holds the cursor over a point)
NB Y-axis scale : 01/01/2009 to 31/12/2009 with increments of 14 days. this will be the same scale used for all projects.
Date Project Delivery Date Status Comments
01/02 x 01/05/2009 Green original date
07/02 x 08/05/2009 Amber delay supply
14/02 x 20/05/2009 Red out of money
21/02 x 30/05/2009 Green on track
28/02 x 28/02/2009 Blue delivered-wow
So id like the macro to draw the line for project x based on the 'delivery date'. The points should be coloured according to the 'status' column and when you hover the mouse over the point the data lable will show up taking info from the 'comments' column.
Would it be possible to create a macro that will be able to generate this graph automatically. I have a few projects id like to do the same thing for.
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
id
event_name
C83-858
sample_1_arm_1
[Code].....
I have Sheet1 "MASTER" and Sheet2 "Area1" and Sheet3 "Area2" etc...
My MASTER sheet has a list of employee names and the areas they work in. I have employees working in different areas, and I want to pull a list of employee names from the MASTER working in Area1 (sorted on the MASTER sheet) to column A on Sheet2, then pull a list of employee names working in Area2 to column A on Sheet3, etc...
I want it to do this in such a way that if I add an employee to Area1 on the MASTER data, it will populate that employee in the Area1 Sheet.
So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.
I've attached a sample sheet. I want Column A in the Area1 sheet to reflect all names that show up on the Area1 LIST on the MASTER sheet, and nothing else. I'm using VLOOKUP to pull the rest of the data from the MASTER table.
There is a new sample workbook up now. Couldn't update it sooner due to site outage. I've removed irrelevant data to improve readability and focus on what I'm trying to achieve. Again, the main issue is scraping column E from the Master, and populating a list of all employees who match certain values in Column E on the Master in Column A of the other tabs.
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedFiles :- [url]
i have upload 2 worksheets (in a zip file)
i have attached my 2 files,
1 is report of Feb 2007 & other is March 2007
on 1 Branch,
i got 200 Branches reports & i have to compare the people recored and have to identify following things
1) in which Days bukket is fall in Previous report ( compare with current)
2) if the Account # doesnot match then it should be HIGHLighted!
what i have to do is to compare the Account # with Previous & add a New Column & write the previous days (in number) plus highlight or show some remards that doesnot exisits in last report,