I have 2 sheets with a formula in sheet 2 looking to pick up information from sheet 1. the formula in sheet 2 is:
=HLOOKUP(C8,'Cost-Effort'!$B$7:$AH$42,26, FALSE)
But C8 appears in W7 and X7 in sheet 1 and will only pick up the first Occurrence and I need it to pick up the second Occurrence for this specific task.
What is the easiest way to find the last occurrence of a value in a table using functions. I want to avoid VB if at all possible and note the row number and use it in an index function to report text adjacent to that last occurrence. I would normally use match but match only records the first match and not the last.
View 6 Replies View RelatedI have a spreadsheet that lists all the work done by employees within a specific area. Some employees cover multiple areas.
I am now needing to work out the average work completed by each area. I need a formula that will count the number of employees by each work area.
I know this is something I have done before, but my mind has gone blank and I can't for the life of me work it out again (it's one of those days).
Row 3 of the spreadsheet contains the codes for the work areas they cover (CM, V & TC) and some employees only cover one (which would be a simple CountIf) but some have multiple.
What is the formula to, for example, count the number of people who have CM in row 3 even if they also have other entries in that cell.
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
I'm a fairly new user to using VBA and I'm currently running into a little snag. I'm trying tu create userform which will let me know if a person already exists in my sheet by looking up the last name in "A", then looking up his first name in "B", if such entry exists then display a message saying " Yes". If entry user has not been entered in the sheet then use a combox, to select the reason , then choose a date ( i have a working date picker ). Then entry should be entered in sheet (Name, Surname, Reason, date).
Whats working, I can use all of the above to enter all the info into the sheet, what I need to do is verify if user is alread in there, if not, then add it. The so on and so forth
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
I have this excel workbook with VBA code that starts when the workbook is opened.
Every day at 12:00 the windows task scheduler opens the file (hidden). The problem is that my computer is not always turned on at 12:00 hence it would be better if I could store the file in the cloud and run task scheduler from there.
In an optimal world there also has to exist a dropbox connection.
I have a PDF sheet with info laid out like this:
Code:
Peter Paul Mary
Text Corporation
One two three street
Mars, New York 90000 USA
phone: (111) 555-2222
email: tellmewhy@yahoo.com
There are 10 names and addresses like this on each PDF page and the entire PDF file has 50 pages so to manually copy and paste each bit of info into separate cells on the spreadsheet's appropriate cells "Name, Company, Address, State, Phone, Email" e.t.c is too difficult.
I have a large spreadsheet set up with multiple rows, I have the formula -
=a5&"repeated text"&c5
this brings back 2 results
test repeated text test
repeated text
In the instance this is the latter with no combined cell vale, how would I mass delete them?
I need a macro in a excel for the below scenario:
1)i have merged cells from A1:B3
2)if i click on a macro the output in the above cells should be as follows"
"Login to database"
3)if i run the same macro for the 2nd time the output should be:
"Are you done with the reporting?"
4)if i run for 3rd time the output should be:
"Logoff from database"
5)Say example if i run the same macro for 1o times i should get 10 different outputs....if i run for the 11th time the output should get repeat from 1st....
6)Output need not be in order....random also fine...
If possible gimme 2 different codes:
1)Output should be in the order wise from 1-10 and get started from1 again
2)Output should be random....
I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website .
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday.
is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?
I need to work with a spreadsheet with 15000 rows of data. By the time I link this file with my final file and add formulas, my file has reached a massive size and the following pop up error message appears.
View 3 Replies View RelatedI am using code that creates a new workbook and then copies a number of sheets to the new workbook. I am using Application. ScreenUpdating = False but as the sheets are being copied over, the Excel task bar button jumps around.
View 2 Replies View RelatedI am trying to create a task list from a master matrix of items to be completed. When fully populated the maters matrix should have approximately 200 items to be completed over the year. I want to create a monthly to do list in EXCEL based on the master matrix. Some reports will be due on various timeframes. The reports will also be assigned to different workers. I would also like to be able to provided brief instruction for each task. Items will be added and subtracted from the master list based on changing requirements. I would like each month to be a different tab in the workbook. Attached is a spreadsheet that better explains what I am looking to create.
View 7 Replies View RelatedI've used Excel 2003 to write a macro to analyse some date from our internal proprietary database. I use some Perl to generate a bunch of csv files from the data, and the run the macro to generate graphs and statistics from that. What I would like to do now is run this entire operation as a scheduled task, so that, every week or so, the data is downloaded, the processing is done on the csv files, and the results are saved to a location to be viewed on our internal site.
I'm assuming that the best way to do this would be somehow run something from the command line (to be embedded in a script) which basically says "1) Open this csv file 2) run this macro 3) save the output to this location", but I can't figure out how this can be done.
I am trying to write a macro that will allow me to add task to [url].... (task managing webpage) from data within excel. I could easily write a macro that emails the information to my toodledo email address, but this is not what I would like to do.ow to start the code utilizing Toodledo's API, which can be found here:
View 5 Replies View RelatedIve been looking at a bunch of examples, but everything that I have tried just gives me errors. what code to use to make a vbs script to run in windows task scheduler that will...
Open "J:GroupsBSHEETSSDANew EOD.xlsm"
Run the macro named "EODPrint"
Save the workbook
and close the workbook
Leaving excel open if other instances exist.
Can a macro from Excel VBA create a Task in Outlook?
I would like to have Excel create a task populated with a date in a given cell in Excel.
Basically, click a button and a task is creaed in Outlook based on the date in a given cell.
Is this possible?
I am trying to make certain changes to the page setup on all sheets in a workbook. Here is my code but something is not working. I think I need to somehow select the next sheet because the macro is only performing the task on the active worksheet. My Visual Basic skills are somewhat limited so I'm sure this is not the most efficient way.
View 3 Replies View RelatedWhat I do know is how to set up a task in Task Scheduler and I have it set to open my Excel file: Workbook1.xlsm. This task is set to run at 7am each morning. Workbook1.xlsm has a bunch of different macros in it.
What I'd like to do is to have each of those macros to run automatically when the file is automatically opened by Task Scheduler.
i have a validation list in A5 pointing to F6:F, i need the refmask_no to change according to the validation list in A5:A.
So if i choose from List LEADCASH the refmask_no= '100007995' etc
How can i automate the changing of refmask_no?
Sheet1  ABCDEF4Validation ListCorr _acc_no  Mapping 5LEAD CASH ABNAMROrefmask_no = '100007997' Â
Corr_acc_noLead Set6Â Â Â Â '100007995'LEAD CASH7Â Â Â Â '100020741'LEAD CASH ABNAMRO8Â Â Â Â '100020645'LEAD CASH AUDNZNR9Â
I currently use task scheduler to open a a spreadsheet at 4am every morning and run a macro. This macro refreshes data pulled from an accounting software. The macro also closes the spreadsheet at a specific time. After this is complete I have task scheduler open a separate spreadsheet and runs a macro to pull the data from the first spreadsheet into a dashboard presentation of sales data.
This all works fine...what I am looking to do is make sure both instances of excel get closed. Currently, this whole process is complete around 6am and when I get to work around 7 or 8 am there are two instances of excel still open. The workbooks are closed but excel is still open. When I am in the office I can just close these instances of excel but if I am out of the office these two instances of excel stay open and mess things up for the next mornings refresh.
Is there a way to make sure both instances of excel get closed? I am using the code below to close the spreadsheets.
Code:
Sub Closeworkbook()
ActiveWorkbook.Close True
End Sub
I have a repetitive task that needs to loop effectively. The only things that change each time are the start Columns ("C:C") next moves to ("D:D"), this then continues down the different columns, until it reaches the end of the columns.
The other thing that changes is the formula initially it starts at RC[-1]&"",""&RC[1]. Next time the numbers will be plus one, so RC[-2]&"",""&RC[2].
The last thing that moves is the "B1:B" & LR on the next step will move to "C1:C" & LR.
Basically a loop with variables in the loop. Is this possible? Below is the code I have begun to write but I could be writing this for ever, over and over again.
Code:
LR = Cells(Rows.Count, "A").End(xlUp).Row
Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"",""&RC[1]"
Selection.AutoFill Destination:=Range("B1:B" & LR)
[Code] .......
What I am trying to do is record the time when a task is completed on the spreadsheet. For explanation sake, lets say I have list of 10 tasks in column C 1-10. In column B 1-10 I have a button for each task. When you press the button for the task, the time it was pressed is recorded in D 1-10. To put the time in I used =now(). The problem that I am having is, when you press button 2-10, it changes the time in the cell above to the same time. So instead of have 10 different times in 1-10, I have 10 identical times,being the last button I pressed. I tried timestamp, time value, etc
View 1 Replies View RelatedGiven:
6:21
3:04
2:29
0:48
These represent time it takes for a fax to send. I'm trying to find the average time it takes for these faxes to send. Using the built in @AVERAGE option does produce a number albeit an incorrect number. Wondered if I could convert these to decimal numbers then covert the result back to time - when trying that I don't get an average but rather a time of day - say 5:55:30PM.
I'm trying to construct a gimmick which will represent how many day’s my little girl has been alive and am having some trouble trying to figure out which route to take in trying of constructing my timers
I'm trying to represent how many seconds my daughter has been alive in real time,
She was born on 01/06/2005 and I want to be able to see the ticker ticking away on my screen rather than it being static
In fact I would like to illustrate how many years, months, weeks, days, and seconds Loren has been on this earth,
If successful I would like to take it one step further and do this with words as well as figures
code for VBA Excel that
Starts Processes
End Processes - Already got one but not that good.
View Processes
I am trying to build a worksheet that is sort of a Project Plan. Why am I not using Project - simple - not everyone in my company has Project, and exporting in Project 2007 is just nasty!
The issue can be described with a simple example:
Imagine the sheet with the following columns
Task Ref - Task Description - Dependency - Start Time - Duration - End Time
Duration is in Minutes, End time is calculated as a formula adding the Duration to the start time using the TIME function.
Dependency contains the Task Ref of the task that must complete before the task in question can start. I would like to have a formula for start time, that pulls in the value for end time of the task identified in the Task Ref column -
I am running into a problem with my code. It sounds almost exactly like a previously posted problem 2 Copies of The Same Workbook Open in Task Manager but I never saw a fix.
I am also getting two copies of a workbook in task manager. It appears when I do a Workbooks.Open filename.
Is there a way to create user defined task panes from vba ?
View 4 Replies View Related