So now, I want E19 of the table below to bring back column N of sheet 1, but only the last instance in which those Employee 1 and Client Assistance/Request met. In this example N7 of Sheet 2.
Basically the first worksheet (equipment list) is set out for parts used for each individual unit which can be printed out for each unit.
The second worksheet is an equipment list, where each part number and quantity required is displayed which can be sent to suppliers for ordering.
At the moment I
-> copy the equipment list to a new worksheet -> do an advanced filter for "Unique records only" -> =SUMIF('Equipment List'!B:B,'Parts List'!A16,'Equipment List'!D:D)
which is much faster than the way we used to do it.
The problem is, when I add a part to the equipment list that is new, I need to go through the process again.
Is there a way to automate so any parts I add to the equipment list, if it is the first time the part number has been entered, it will copy to the parts list worksheet and update the qty column or if it is an existing part number it just updates the qty column?
The equipment list may potentially have up to 100 different drives, but using mainly the same equipment.
I created this sample on an old desktop using excel 2003 but I use 2010 on my laptop.
In Excel 2003, I remember being able to tab across a row of cell entering data and when I got to the end, I'd hit enter and it would take me to the beginning of the next row. But in 2007, I can't...is there something I need to do in Excel Options or is this function gone?
I am looking for a formula to place in C2 that will macth the number in column D with the number in column A and return the LAST date/time from column B that matches the day from cell C1.
If correct, C2 would return 01-02-2012 3:15 PM (Last date/time for 10055 on 1-2-2012) C3 would return 01-02-2012 9:00 PM (Last date/time for 10062 on 1-2-2012)
In column Y, I have a list of employee numbers (there are a random number of blank spaces in between each number)
In column AE, I have a list of clock in times (there are a random number of blank spaces in between each clock in time)
In column AY, I have a list of the same numbers from column Y (there are no blanks in this list)
Column BB. I am looking for a formula to place in cell BB2 and copy/paste down that will match the number in column Y and return the first non blank cell from that point in column AE.
Example:
10062 is employee number in cell AY2. Her employee number (10062) is also located in cell Y5. Her clock in time is located in cell AE7.
10099 is employee number in cell AY3. Her employee number (10099) is also located in cell Y14. Her clock in time is located in cell AE16.
What formula can I place in BB2 so that it returns the clock in time of employee 10062. Then copy and paste so that BB3 returns the clock in time of employee 10099?
I need a formula that returns the Nth number from a single column that contains a small number of values within a span of 1000 cells. E.g., within 1000 cells there are 36 with numbers and the remaining cells are blank. I need to obtain the first occurrence of a number in the column, then the second, third... to the thirty-sixth. The end result would be a column with 36 numbers with no blanks in between without loosing the correct order of the numbers.
I have excel 2003 and have been tooling around with the index and lookup functions with no success.
I have a list of product numbers in col. A. In col. O I have a list of file names that contain the product numbers as well as additional characters. I need a formula that will search col O for the first instance of the text in cell A2 and return that value.
The next formula will return all instances that contain the text found in A2.
I have the following formula that returns the most frequently occuring string in a range. what I would like to do is return the 2nd, 3rd 4th and 5th most common occourance as well. I am not sure if I can do this by adjusting this formula or whether that would be a completely diffent formula or worst case senario it is not possible at all.
I am working on an application for a monthly sports tournament that I run. There are three workbooks to this application: the Administration workbook, the Bracket workbook, and the Leaderboard workbook. I currently have a dual monitor setup (extended, not clone) with my laptop in front of me and a large TV on the wall for the players to see.
In order for the players to see the bracket and the leaderboard, I open excel, then I open the Bracket.xlsm workbook, then the Leaderboard.xlsm workbook, and I drag those to the TV. I then open another instance of excel on my laptop, and open the Administration workbook. This stays on my laptop and the players cannot see it.
Here's what I want it to do:
In less words, the administration workbook controls the other two workbooks. Every time a player reports a win/loss, I enter that into the Administration workbook, and that needs to update both the bracket and the leaderboard, so the players can see it on the TV. So, basically, all the data is in the administration workbook, and the other two serve as displays for that data in a format the players can understand.
Here's my problem:
When I run a macro that is supposed to update the other two workbooks, I get an error: "Runtime Error '9': Subscript out of range." I believe this is because of the seperate instances of Excel. Since Bracket and Leaderboard are in a different instance than Administration, it can't 'see' those other two workbooks. Below is the part of the code that deals with these three workbooks: ....
In the last couple of weeks my copy of Excel 2010 has started acting strange. Now when I double click an xls or xlsx file in Windows Explorer, it tries to open a new instance of Excel, whic means that it tries to reopen Personal.xlsm and Personal.xlsb, and finds they are already open, so it gives me an error message. Previously it perfectly happily opened the document in another window in the same instance, which is presumably much more memory efficient quite apart from the file locking problem mentioned. How do I get it back to the way it was before? Is this another of MS's famous overnight downgrades?
I use the following code to open an existing open workbook in a new instance of Excel...
Everything works fine... What I would like to do is close (when I say close I mean, completely exit/quit the application). I'm not able to do that, the original Excel instance stays open with no spreadsheet open. I would just like it force the application to quit so that after running the code I would just have one Excel instance open (with the desired workbook, which works fine now)...
Public Sub Re_open_workbook() Application.DisplayAlerts = False ThisWorkbook.Save
I am running Excel 2010. I have two separate instances of Excel running. I select data and copy it, and then paste it into the other excel spreadsheet in a separate instance. The clipboard data DOES NOT paste the desired data, instead it pastes the item most recently copied in the separate Excel instance that I am trying to paste into. It's as if the excel instance has it's own clipboard, and is unable to use the same clipboard the other Excel instance is using. Copy and paste functions / clipboard seems to working perfectly outside of the Excel program.
I just used the excellent formula =row(index(range,match(1,index,--(range)>0),0),0))) to give me the first instance of a number, but i am wondering if there is a formula to give me the last instance of the number as well? Maybe? This would save me much time if possible.
Had to rebuild my PC's hard disk and reinstall software. Now, whenever I open another workbook via explorer double click, a new instance of Excel starts. If I open another workbook using Excel's menu open, then the workbook opens in the same instance. I've tried a 'Repair' of Office and resetting the default programs to Excel for all Excel related file extension types.
I would really like excel to open files in a new instance of excel by default. Currently the only way I can do this is by starting excel and then File->Open. While this is only a little bit of extra effort, I've recently gotten in the habit of opening and closing 100s csv files daily and the wasted time really adds up. I've read several threads here, but mostly found people trying to accomplish the opposite of what I am.
I don't have to keep using the ridiculous Substitute() work-around when I need to grab cell text after the nth instance of a character. In doing data cleanup I'm constantly having to grab text between, say, the 4th space character and the 6th one. Since Search() doesn't have an instance parameter, I end up having to use the 'Substitute trick', since it's the only string function that does have one.
See below for a typical example of the convoluted formula I need, and another example of it if Search only had 'instance' available. Simplifies the formula quite a bit.
*Typical real example, here to find the word between the 4th and 5th spaces in A1:
I am looking for a way to move workbooks to a new instance of excel by the click of a button. For example if I have multiple workbooks open under one instance of excel I do not want to have to minimize this and that... I have multiple monitors and I would like to hit a button on a selected workbook and have it auto open it to a new instance of excel. I image this may not be apart of excel and that it might require a macro to do so.
I need a conditional formatting formula that will highlight the second instance of a combination of values from two columns.
For example, Column A is a person's name and Column B is a drop down with 3 options (Street Address, Phone Number, and Email Address). If Row 2 says John Smith (Column A) and Street Address (Column B) and Row 6 also says John Smith (Column A) and Street Address (Column B), what conditional formatting formula would I use to highlight Row 6 as duplicate information?
I have multiple buyers that use an Excel workbook containing several macros to perform edits on other workbooks they receive via email. Generally, they have the workbook containing the macros open, and they can double-click the workbooks they receive in the email messages to open them, and the macros are available from the "master" workbook.
Recently, however, some of the buyers have been getting new computers running Windows 8 and Office 10. Now when they have the "master" workbook open, and double-click the attached workbook in an email, (Outlook 10), the new workbook opens in a new instance of Excel, and as a result the macros are not available to run on the newly opened workbook.
The work around has been to right-click the attachment and save it to their desktop, and then use the File/Open command from the "master" to open the new file. This is a hassle and takes extra time.
I'm sure I am overlooking a setting somewhere that tells Excel to always open new workbooks using the same instance of Excel, (there are two people using the new systems that aren't having problems, and two that are), but for the life of me I can't find where to set these parameters.
I have a VBS script that runs a macro in a workbook that is located in a SharePoint doc lib. The code looks like this:
[Code]....
I use code exactly like this to run other queries and it works great, but for some reason when I run this macro the instance of Excel doesn't end after the script is done running. I can see EXCEL.EXE sitting in the Task Manager and I have to end it manually.
Here is the code for the macro and related macros:
This macro just calls my other macros. Each terrslicersXX macro is a different sales territory for which there are slicers to filter out data.
[Code]....
An example of one of the sales territory slicers. Basically it just sets the slicer for the required sales territory so the SaveAsWebpage macro can save the information off as a webpage.
[Code]....
Saves the current data as a webpage.
[Code] ....
I have a feeling it has something to do with the last macro and how I publish the sheet as a webpage but I still can't get it to work.
I am working on a data entry sheet time and I am running into a wall. I am trying to code intelligence into sheet so that the user doesn't have to enter as much data.
I am using Excel for Mac, 2011, 14.2.3, But I would like to be able to have others use it on PC and in Excel 97 - 2004
I am developing a time sheet for work. I am trying to have excel generate the work order # for subsequent jobs.
'Daily Entry Sheet'!$H$8:$H$138 = Work Order Number
Following is what I presently have coded.
The following Works but is not completely as I need.
=IF(OR($F8=0,$F8="",ISNA(G8),$H7="Work Order #"),"",IF(ISERROR(INDEX($H7:H$8,MATCH($F8,Prop.,0))),"",INDEX($H7:H$8,MATCH($F8,Prop.,0))))
This works fine in that the match finds the first instance of the work order #. The problem is there may be a later worker order # for the same property which supersedes the old work order. I need to find that last work order number for property # 919 for example, not the first instance, as the index/match returns.
I looked for a function similar to the sumif or countif
=SUMIF(Prop.,$F4,WorkOrdNum)
=SUMIF(Date,"="&$V6,Total_____Hours)
But I don't need to add the work order numbers.
Countif can give me how many instances property # 919 is used,
But I haven't figured out how to use that to It would be ok if the function would return the largest number like the MAX function, best would be to select based upon the latest date.
I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.
The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.
If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?
In Excel 2003 I have Manually Grouped a number of rows. (Data>Group and Outline>Group).
If I use the buttons with the numbers 1, 2, 3 (indicating an outlining level) to collapse or expand the outline I receive the Error Message: "Cannot shift objects off sheet."
If I use the collapse button (-) or expand (+) button the data collapses and expands as I would expect.
I am aware of the normal issues surrounding objects and outlining and have ensured that I have changed the properties of all my of the objects in the sheet to "Move and size with cells". There are no comments in the grouped rows.
