I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open
Private Function GetValue(path, file, sheet, ref) Dim arg As String If Right(path, 1) <> "" Then path = path & "" If Dir(path & file) = "" Then GetValue = "File Not Found" Exit Function End If arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) GetValue = ExecuteExcel4Macro(arg) End Function....................
I'm trying to copy a pivot table cell onto another worksheet that has a table with Month-Dates across the top in columns and left-most column has several cells, each with a named range. I get an application error with:
Sheets(shtSrc).rngSrc.Address.Copy _ Sheets(shtDest).Range(Sheets(shtDest).Range(rngDest).Row, Dt.Column) shtSrc and shtDest are Strings passed in to Sub for worksheet name. rngSrc is a Range passed in to Sub. rngDest is the Named Range of the destination cell.
All of the values get passed in; I'm just not using the range properly I think.
I'm not married to what I've got so far. Basically, as long as the pivot data gets copied at the intersection of the correct Date (Column) and Row I'll be happy.
Got something I need to do that I have never done.
I have a column on a worksheet1: NBC//1 ABC//1 ABC//2 ABC//3 ABC//4 CBS//1 CBS//2
I have a column on another worksheet2: NBC ABC CBS
In the column next to the above on worksheet2, i want to pull the numbers in for that (the numbers are contained in ONE column): NBC | 1 ABC | 1 2 3 4 CBS | 1 2
Im not sure how to go about this? In the example I attached, on sheet 2 column B, that is where I want the numbers to build to.
This below code i find out from this form Skip the row find next but i need it i have column fom c to M if the cell k valur or j value = 0 or empty skip the enitre row go next row ifthe kvalue <> 0 or <> empty do something the below code where do i moify .
Set c = .Find(MyInput, LookIn:=xlValues, SearchOrder:=xlRows) If Not c Is Nothing Then Count = Count + 1: ff = c.Address k = c.Row Redim Preserve wsName(1 To Count): wsName(Count) = ws.Name Redim Preserve rng(1 To Count): rng(Count) = c.Address(0, 0) Do
I have a worksheet with three columns. First Name, Last Name and email address. I need to combine the first name with the last name and add our @ourcompany.com to the end of it for the emaill address.
I've figured out how to do that, but my problem is, we don't use our first and last names in the email address. We use first initial and last name.
I know how to make Tom Jones become tomjones@ourcompany.com
What I need to know is how to make Tom Jones become tjones@ourcompany.com.
Can someone point me in the right direction here? I just need the first initial of the first name and combine that with the last name and then add in the @ourcompany.com.
The report that I am trying to build relies on DCOUNTA formulas.
This report has 200 lines and 200 columns approximately. Each of these cells would be a DCOUNTA formula. I have a separate sheet, 'Criteria', where I intend to describe all of my criteria for the DCOUNTA formulas.
My problem is that for each DCOUNTA cell in my report, I have to create 2 rows and 5 columns worth of criteria information... so, for 40,000 cells to populate, I need to create a huge sheet of criteria.
So, while I could do the above, and it would take me some time, more efficient and elegant way to spend my time in order to get this report display DCOUNTA formulas?
For more context, I am trying to present information for 100 individual salespeople, monthly and year to date. For each cell, I need to tell DCOUNTA that the date needs to be comprised in the current period (2 columns), what the name of the salespeople is, and what the nature of the information is, which could be more than 2 additional columns.
I can to some degree autofill most of this, with some OFFSET magic, but its still feels like an ugly way to achieve my objective.
I've been trying my best to create a directory structure using VBA and Excel, and have run out of patience on how best to do this. My goal is to create a directory tree containing 2 levels. The 1st level (Main Folders) will be named from data in Column A of an Excel Spreadsheet, the 2nd level (Subfolders) will be created from data in Columns B, C and D of the spreadsheet. I've been using the following code with success, but don't know how to create subdirectories:
Sub StartHere() Dim rCell As Range, rRng As Range Set rRng = Sheet1.Range("A1:A20") For Each rCell In rRng.Cells CreateFolders rCell.Value, "C: est" Next rCell End Sub
Sub CreateFolders(sSubFolder As String, ByVal sBaseFolder As String) Dim sTemp As String 'Make sure the base folder is ready to have a sub folder 'tacked on to the end If Right(sBaseFolder, 1) <> "" Then sBaseFolder = sBaseFolder & "" End If............
My Question: how would i be able to render a dynamic drop down tree in the following format (see attachment): look at the left Rich Graphics folder example: http://javascript.cooldev.com/script...mos/superdemo/
I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?
I want to create sections in excel exactely like below. I need to show user how many types systems are available and in each type how many config systems available.
I would like to select the Job # in Cell B2 (Job # list) and have Cells C2 filled in from Job Title List and D2 Filled in from Client List and E2 filled in from Job Location list.
I have several worksheets across the bottom of the workbook, lables September, October, November, December, January, Febuary... Each of these worksheets contains a table of identical formatting. (See below)....
how to compare the value in one cell on sheet "Block #1" to a list of values on sheet "Data". Specifically I want to be able to look at the value in a cell (which will be a date) and then compare it to a list of company holidays on another sheet in the workbook and if the date is a company holiday it will display the word "Holiday" in another cell I specify on "Block #1" I am doing this to automate generation of a calendar which will have 13 worksheets one for each of 13 4-week blocks. I can get the calendars generated, but can't seem to be able to get it to compare the values of each date to the list of holidays.
I am trying to build a cup tree that automaticly move players over to the next round if they are first or second in their group of 4. What happens when first person have a result of lets say 33 and 2nd and 3d person have a result of 35 each and the 4th person 37? First person is easy but then there are 2 persons with the sam result and they play sudden death and we put in a 1 in the sd column for the winner and a 2 for the loser of the sudden death.
How do I make my formula figure that if they had the same result go look at the sd column?
Another thing is that of the 2 players advancing they have to be placed in the same order in next round, how can I achieve that?
If you look in column I row 19-22 there are players in the wrong order because the player with the better result goes in first. It should be this order: 9 11 15 16 instead. (if you are ahead in the previous round you will be ahead in the next round)
Another problem I noticed now is that when 2 players has the same lowest score the first player takes both spots as it is now and that doesn't work in real. You see what I mean if you look at player 1 in round 1 and 2 (R1 & R2), in this case it should have been players 1 2 6 and 7 moving on to round 2 and not 1 1 6 7 as it is at the moment.
(Rank is their rank from the qulification rounds where the first 4 is seeded and the rest is drawn into 1 group a time)
Klass B
* B C D E F G H I J K L M N O P Q R
2 Rank Name R1 sd Pl.
[Code] .......
Spreadsheet Formulas
Cell Formula
I7 =IF('Klass B Calculations'!F4=1;'Klass B Calculations'!C4;IF('Klass B Calculations'!F5=1;'Klass B Calculations'!C5; IF('Klass B Calculations'!F6=1;'Klass B Calculations'!C6;IF('Klass B Calculations'!F7=1;'Klass B Calculations'!C7;""))))
I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two. I am using this formula currently, but is returning an incorrect result-- =INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)
this is the record number in M2 BU1HAD80
it is returning BI1METBRSM as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions
I am trying to create an array formula that would list all the corresponding rows that contain a certain value.
Here is an example workbook to illustrate what I'm trying to accomplish:
Book1.xlsx
I need to create a list/table that would show all the row labels that correspond to a value. For example, I need to make a list of all cells with a value of 1 under the "Green" column and list the row label(s) that correspond to 1.
Creating the list would result in following return values for "Green": A,C,E,F in separate rows.
I've been using [URL] ..... as a guide but the formulas did not return a corresponding row label. Pivot tables did not accomplish this either.
Is it possible to do without using an array formula?
I have a workbook with several drop down boxes and formulas already set up and working. I want to improve it though. My question is.... is there a formula that will make the drop down list change based on a cell value....
For example: If B6 equals vegetables then C6 equals list (potato, carrot, pea, etc.) If B6 equals fruit then C6 equals list (apple, banana, grape)
I'm trying match values (and set a Yes / No result) from values in two very large lists.
List 1 (approx 170,000 rows) contains the 'Find What' values List 2 (approx 980,000 rows) contains the 'In What' values
Values in list 1 will be unique in list 2, but not all values in list 1 will appear in list 2. The values in each list are all 16 character stings. This is a one-off otherwise I'd probably import into a AccessDB.
Using formulas is taking an absolute age to calculate using MATCH function, so I'm wondering if coding is the better angle.
Mercedes ________________Mercedes BMW ____________________Fiat Fiat _____________________BMW VW _____________________AUDI AUDI_____________________Jeep Jeep_____________________Porsche Porsche __________________Ferrari _________________________Lamborghini _________________________VW
As you can see, list 2 had some additions (Ferrari,Lamborghini) and a different overall order. I want to input the 2 additions on list 1 right after the last cell(Porsche). List 's 1 order cannot be changed. I have to do it on Excel 2003.
I have two drop down lists, one is named Copayment with values Yes and No and the other is named Coinsurance with values Yes and No as drop down lists. I want to make them mutually exclusive, for example: When the user picks Yes from Copayment, then the Coinsurance to be automatically No and the opposite: when the copayment is No then the coinsurance to be automatically Yes.
I have a list of last names in Sheet1!Column A. I want to see if any of those exist in Sheet2!Column A, and if it does I want to post the data from Sheet2!Column B into Sheet1!Column B.
I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).
The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.
That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.
Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.
The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet
