I am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
View 1 RepliesI got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
Rather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.
View 7 Replies View RelatedHow can I pull data from a specific cell from a closed worksheet in VBA?
Not sure but I think that Workbook("wb1.xls").Worksheet("Sheet1").Range("A2") only works if the worksheet is open.
I'm using excel 2010 on a pc. I need to create a calender. So far I have been using the tutorial for the pop up calender. The result I'm looking for is to set up a specific time frame and have the calender provide the "due" date. I would like the due date to exclude weekends and holidays. Would I be able to do this with the pop up calender?
View 3 Replies View Relatedcode to pull a hidden frame when I click a command button. I have Uploaded a Sample worksheet on my requirement.
View 4 Replies View RelatedI need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
I pull a report every month which has the same name - at the beginning - (i.e. Monthly File Report_06012014.xls but the date is different every month; where we keep a copy of them every month.
So I need a vba to pull the data from that specific file - since I'll have more files open, but wouldn't keep the date since it will be different every month - is there an & that goes at the end or somehting: Monthly File Report &....xlsm?!
i need a macro that pull specific data from word to excel.
I don't know where to begin or if this is even possible. I have report that I need to format.
See example file. Note: real file has 2000 rows.
The book date and book amount in the orginal is the POS date and POS amount in the format sheet. I don't need the "over/short" data from the original. Col. A contains store #'s and they are 2,3,or 4 numbers long preceeded by an "S-". In the formatted sheet I need the "S-" removed. The data is grouped by column C. 3 of the same equals 1. Groups vary.
I have a complex data set that has been imported into Excel from a binary data file and I am looking for a way to simply pull out specific fields. I tried to record a Macro that would simply delete the rows between the data rows I want to keep, but it always references itself to those same ranges and I'm not sure how to identify that I want it to sequentially move down the page deleting the same number of rows, and adding the row of information I want to keep as it goes.
I need 15 minutes removed from a difference in time, if the time span falls over 9:00 am through 9:15 am.
I'm inputting time like 8:30:00 in A1 and 10:30:00 in A2, then calculating the difference in A3 (2:00:00). Is there a way to remove 15 minutes from that 2:00:00, since it falls over 9 to 9:15?
I have a formula that calculates only seconds and frames. The frame rate is 75 fps (0-74).
e.g., 49.50 is 49 seconds 50 frames. It will not parse Minutes.Seconds.Frames, e.g., 1.49.50 is 1 minute 49 seconds 50 frames.
What my existing formula does is converts the number to all frames then converts the answer back to seconds and frames.
I need this formula to include minutes in its calculation.
A copy of the spreadsheet is here: ...
How to create repeated time frame within a column like example stated as below:
Time 9:45AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
I need to be able to calculate the actual number of days worked for an employee over a specific date range. I have a data sheet containing employee, transactions, and date of transaction (date only, does not include time). The actual number of days worked may fluctuate due to time off or holidays, so I need to be able to calculate this individually by employee. For example, with columns Employee, Transaction, & Transaction Date:
123 ABC 07/01/2009
123 ABC 07/02/2009
123 DEF 07/02/2009
123 GHI 07/02/2009
123 ABC 07/03/2009
123 ABC 07/07/2009
123 DEF 07/07/2009
123 ABC 07/08/2009
123 ABC 07/09/2009
etc....
Should return 4 work days for employee 123 over the date range 07/01/2009-07/07/2009
I am trying to do a timesheet spreadsheets that lists employees clockin and clockout times
Name Start End num hours worked
Cory 02:00 04:00 2
Jack 23:00 05:00 6
Fred 10:00 17:00 7
and then go through the list and and count the number of employees in a certain range.
time range number of employees working
05:00-06:00 1
06:00-07:00 2
07:00-08:00 6
08:00-09:00 5
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00..............
Im trying to count people working during times of the day. They work shifts from 8am to 9pm. I need to know at each 30 minute interval of the day how many are working. IE at 12:00 or say 13:30 I need to know how many are working. I also need to be able to account for people that are sick. IE If a person works during the day I wouldnt count them as sick at night.
Im data is set up like
Column A agent name
Column B agents start time
Column C agents end time
Column D holds two variables either sick or support.
I have found a thread that counts the people working but doesnt account for the sick or support variables. I didnt link that thread as I thought it went against the rule "STATE WHAT YOU WANT TO DO, NOT WHAT YOU THINK YOU NEED TO ACHIEVE IT "
Drop down menu that can pull data for 1 person at a time from Table?? (Even possible)
View 1 Replies View RelatedI'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working).
What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.
The spreadsheet will be have 3 worksheets. The first worksheet will be the mastersheet (Form-01 in the attachment) that will be used as a summary/receipt for the person participating in the auction. The second worksheet will be a list of the people participating in the auction (Names) will all applicable contact info. The third worksheet will be the list of all the items for the auction (Auction Items) with all relevant information per item and a unique number for each item.
The idea is to be able to track information during the auction on the "Auction Items" worksheet, there is a column for the bidder ID of the winning bidder. Then after the auction is over and its time to settle up be able to go to the master sheet (Form-01) and enter the Bidder ID# and have the other fields auto-populate.
I am trying to pull data from one worksheet to another. I am using Product ID numbers. The problem I am having is that not every Product ID I am searching has a partner on the second list, so I get an #N/A. In stead of #N/A I just want a "0".
my vlookup looks like this: =IF(VLOOKUP(A1,Sheet2!A5:C500,3)>0,VLOOKUP(A1,Sheet2!A5:C500,3),"0"). If A1 does not find a match on Sheet2, it returns "#N/A" when I want it to return "0".
I have a workbook, in which I will have approximately 5 worksheets. The 1st worksheet will be a summary table, which will pull data from the other 4 worksheets. These 4 sheets have data of 4 companies, thus representing 4 companies. In the 1st worksheet, I have a drop-down list that has selections for each company.
How do I make it so that when I change the selection from A company to B company, the data in the summary table will automatically pull data from corresponding worksheet? For example, if 2nd worksheet represents Apple Inc, the 3rd represents Microsoft, how do I reference from 2nd to 3rd worksheet automatically when I change my selection from Apple Inc to Microsoft in the 1st summary table sheet?
I am having a problem pulling out individual data from previous worksheet (Sheet 1) to another worksheet(sheet 2, 3, 4...) individually.
For example I want to pull out the values of IP B0b/C0b Hum to another worksheet separated by each day. is there an easy way to to this using a vba code or excel formulas?
Please find attached file.
I've been using the DSUM function to pull in data from another worksheet.
In brief, the problem is that when I Cut & Paste the formula from C7 to C8, I want the Column reference to change but not the Row reference.
If I copy across the Columns then the result is OK – e.g. The Formula will change from…
#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!A$17:A$18)#
To…
#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!B$17:B$18)#.........................
I need some VBA code that puts in formulas to pull data in various columns within row 2 of another worksheet.
The formulas need to be entered below some other data, which is populated from another prior macro.
The below code works well but as the prior macro can populate a different number of rows on different days, the Row count then also changes and doesn't always refer to row 2 of 'HLDRT before' tab.
ActiveCell.FormulaR1C1 = _
"=IF('HLDRT before'!R[-22]C[-18] = ""A17"",RIGHT('HLDRT before'!R-20C[14],3)&'HLDRT before'!R-20C[9]&'HLDRT before'!R-20]C[-13],"""")"
Is there a way I can tell the above code to always use row 2 from the 'HLDRT before' sheet, while keeping the column number lookups the same?
I think there is a way to do it by removing the [] signs but I can't get it to work. I also then need the macro to copy these down so I'm not sure if using $ will cause problems?
I am trying to use the indirect formula to pull in data from a cell in a different worksheet using the tab name as my reference. For example:
I have my tab name in cell A2, then I am using this formula to pull the information from cell g29 on the tab listed in A2, but its not working:
=INDIRECT(A2&ā€¯!ā€¯&ā€¯g29ā€³)
Also, my tab name in cell a2 is a date, does that make any difference? I've tried a couple variations on the indirect formula and have gone from the #REF! error to the #NAME? error.
I have 2 worksheets (Sheet1 and Sheet2). Sheet 1 contains around 3000 records with multiple fields (columns). Column A contains the ID number. Sheet 2 contains some records with different fields except for the ID number which is common.
I need a macro to retrieve information from sheet1 and copy it to sheet 2 based on the ID Number; i.e the macro needs to get the ID number from sheet2, locate it in sheet1 and copy the data from column D in sheet1 for that particular record and paste it in sheet2.
I am trying to create a document that runs a scenario based on the two drop down choices selected that determines which worksheet in the workbook autopopulates over to sheet one based on the selection. I tried doing an if statement but did not have any luck.
View 1 Replies View RelatedMy query is;
I have 2 different worksheets having similar format (header rows/columns) but different data (values). I need to pull out these data based on their values, means if cell is blank leave that cell and move on, but if cell has some value then pull that value with its corresponding row & column headers to place into a simple table to further use it for PivotTable. The number of rows and columns are equal in both worksheets.
So basically, the proposed function will run a check on both worksheets within a specific range of cells for their values and if not blank, it will fetch that cell value along with its relevant row header and column header and place all these attributes into the destination worksheet.
Hope I am quite clear with my query. with the required function to ease my life?
I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.
View 3 Replies View Related