I have a worksheet with 30 sets of 3 columns, each column has 20 to 30 rows in use for the same realitive data. I want to get the sum for each 1x across 30 sets of the columns. I am checking vs if it says Projected or Actual. My Visual Basic macro ability is very limited at best. I have a totals row for each row of data so i can see totals for 1x over 30 days, 1.5x over 30 weeks, and 2x over 30 weeks.
Here is the basic idea of what i am dealing with:
A B C
Projected (merged across A, B, and C)
1x 1.5x 2x
8 4 1.5
7 0 0
8 2 0
8 4 5
Again, this represents one day, and I am trying to track at least 30 days across 90 columns. Also there are anywhere from 20 to 30 people being tracked at any one time so this continues to many more rows depending on the job.
I have been asked to edit a worksheet for a client, they want me to create a dependent drop list using existing data from three columns.
I have created a dynamic named range and used and index to create the drop down list, which seems to work correctly. I run into a problem with I try to add the list to more than one column, as it obviously pulls in values from the Helper sheet I have used.
When the user tries to select values in row one after making a selection in row two, the values available for selection are incorrect.
Is there any way to have a three column dependent drop list that can be used in more than one row?
I have uploaded the file here: ownCloud
The information on the tab named 'Cabling Ducts' is used in an index on the 'Helper' sheet to remove duplicate values. The drop down list columns appear in 'CS 1' under the headings 'Cable Type', 'Size (mm)' and 'Cores'. Helper sheet is self explanatory.
I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
I have one sheet that uses a very simple formula for copying certain values from one cell to another sheet =VALUE(Sheet1!B5).
My problem is that rows have to be deleted in sheet1 as the information eventually becomes expired and the following is displayed in my cell in sheet 2 =VALUE (Sheet1!#REF!) Is there a work round this, to prevent the formula showing a #REF! Value.
Row 5 is replaced and the same formula has to be re-entered. Excel 97 used in our office unfortunately.
I am needing to create 2 drop downs that are dependent on 1 drop down. I have named lists that are on another worksheet. I've gotten so far as getting the 1st 2 drop down lists work but my third drop down I just can't figure out what the formula needs to be. HELP!! I've been working on this for a week now and I'm losing my mind. I've checked the contextures website and it does NOT answer this question.
I have a dataset that, due to the nature of how it is generated, separates certain orders into separate rows. Which orders these are is irrelevant, I just need a quick and easy way to make excel re-combine these rows in to one entry, and sum up the data in one particular column in the process of doing this.
The separate entries of a single order can be identified by the data in 3 columns matching - I have attached an example.
The constituent parts of the same order can be identified when the country, city, and code columns all have matching data as you read down. E.g., where
Country City Code a 1 12345 a 1 12345 a 1 12345
Where this is the case, I want excel to automatically sum the count of shops for this order and then delete all but one of these rows, with the remaining row now containing this summed figure. Failing this, a new worksheet being generated/a new column giving me this data would be good.
I imported data into Excel. The first row is a header row in A1 it is fine until the next header row in A12, A23, A34 all the way down to many rows. The problem is the header rows are overlap with other data by seperating with couple of spaces. I need a VBA to move a whole header characters to it own row just right after couple spaces. See the attached for more detail.....
I am attempting to create a form in excel with dynamic data validation fields. What has me stumped is that I want to create multiple complex dependencies (for instance, if you select something from a drop-down list in the first field, that then gives you a specific range in the second field. Then based on what you placed in the second field it gives you a specific range in the third field.). What I can't seem to work out is the formula for making this work.
Is this impossible? Also sometimes the third field my be blank.
Here is an example of what I am trying to accomplish
There are other dependent drop-down list posts on this forum, but none of them are talking about working with multiple worksheets. I have three worksheets, one called "Main", one called "Universities", one called "Schools".
One the "Main" worksheet, I have two Combo Boxes (the one from the "Form" toolbar). I would like to first pick a university from the combo box and then based on the selection, the second combo box would give me just the schools in that specific univeristy. My guess on how to link the two drop-down list is by the ID column. If I select "University of Washington" which has an ID of "WAS" it would narrow down the second list to ID with "WAS" in it (Which would be "WAS1", "WAS2, etc). The reason I am using Combo Box is because of the "Link Cell" property which give me an output of the number of the selection into a cell. I will use that to get the corresponding ID. I have attached an example to this post.
I've looked at the data validation tips and understand how to make a second dropdown dependent on the 1st, but how about a 3rd dependent on the prior 2 selections and so on.
I would like to have dropdowns based on a dataset of 4 columns (Location, Department, WorkGroup, Employee) that frequently gets updated. I would like to have the user select a location then a department then workgroup and employee. I would then fill in a worksheet with other pertinent information. I can get the 1st two dropdowns to work, but haven't found the key to being able to continue to drill down based on the 1st two options.
i am trying to come up with some code to print many sheets in a workbook based on user clicking a button on each sheet.
I would like the code to be reusable and based on the activesheet, this way I don't need to write code specific for each sheet.
My first thought was to use something like ActiveSheet.UsedRange, but the rows may span several pages, so I'll need the header row to repeat on each page
Here's a snippet I recorded
ActiveSheet.PageSetup.PrintArea = "$A$2:$E$12" With ActiveSheet.PageSetup .PrintTitleRows = "$2:$2"
But I can think of a way to use UsedRange but drop out about rows 1-4 which do not need to be printed on the reports
Number of Cols varys on every sheet as well and diff start/stop cols Ex one sheet A:G, another sheet C:T
I have two header rows on a sheet that I need to sort starting on Row 3, Column A.
The sheet is A-K and rows 1 and 2 are headers
Row 1 has merged cells of A-F which says "Information" and then G-K " Tracking Section" Row 2 is broken up into individual sub-headers in columns, A-K each one having a bit more info for instance:
Under the "Information" merged header in row 1 there is A2 Name B2 Work Center C2 C/O date D2 Due CC E2 Due MX F2 Date Received
and G-K in row 2 has different sub-headings.
I have tried to use my normal code, which is great for one header, but it ends up sorting the sub-header as I call it...
Situation: I have two drop down lists 1) Country and 2) States/province
Country has list: [US, Canada]
If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list
Scenario: First, I select "US" and choose the states to "Florida" Next I change the country to "Canada" and forgot to choose province Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec], BUT the current value is still "Florida"
Probem: Now I have "Canada" and "Florida" selected in the sheet
Question: If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.
I'm using Macbook Pro Microsoft Office for Mac 2011
Please see attached file
The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.
The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.
Example: Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33 Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"
So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C
The Value column would then show a value based on the options selected.
So, I've created a pivot table and need to use one of my values in both the row and value fields. I haven't seen a way to easily do that so far. Is there something I'm missing or is this not possible with Excel (yet)?
I have to the following lines in the header (left side). Canadian Mutual Fund Industry Gross Sales By Fund Company As at January 31, 2006 (FYTD Tier 1 Firms) (C$ 000's)
the date is Strored in strDatey. the problem I m having is that I don't know how to make muliple lines in the header through the code.
Private Sub CommandButton1_Click() Dim strHeader As String Dim strDatey As String Sheets("FYTD Gross (Tier 1)").Select strDatey = "April 20" MsgBox (strDatey) strHeader = strHeader & "Canadian Mutual Fund Industry " & strDatey ' text strDatey
ActiveSheet.PageSetup.LeftHeader = strHeader End Sub
I want to filter my dataset, select column B & C to copy, but exclude the header row. I am having a hard time offsetting the selected range. I get a compile error on what I try.Here is my code (yes, I know, its a little juvenile and I will clean it up, but I need to see things clearer at first):
My excel contains first 5 rows of heading information and the rest of the rows contain the data. I want to select a specific row from the data and run a macro that would delete all other data containing rows.
This is what I have now:
Code: Sub DelRows() Rows(6 & ":" & ActiveCell.Row - 1).Delete Rows(7 & ":" & 65000).Delete End Sub
Everything is fine if I select any data row except the first one (R6). In that case, the macro doesn't work as it should, deleting a row from the heading ones.
What I am looking to do is this... (using Excel 2007)
I need to have more dependent lists based on the selection in Column A. (My column B is already set up and works perfectly using INDIRECT)
For example: Column F would be a dependent list of colors based on the sales rep chosen in Column A Column I would be a dependent list of managers based on the sales rep chosen in Column A Column M would be a dependent list of part numbers based on the rep chosen in column A
without using VBA? (If I HAD to - okay, the other person maintaining the sheet would be lost....) I haven't been able to find anything close to what I want to do online - maybe I am just not using the correct terms.
I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning. The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:
00 - Preconstruction 01 - General Conditions
I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.
Second thought:
If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work? Ultimately I want to use these 2 drop downs for reference functions on a second worksheet.
What I am trying to achieve is using the above can it check
C4:C7 Grouped and named "first" C8:C10 Grouped and named "second" C11:C13 Grouped and named "third"
If one from each group is selected it returns 1 value, if 2 of each are selected it returns a second value and if 3 of each are selected it returns a third value?
I thought by grouping them it may give me the desired results but all it does is change if I select more than one from any of the groups.
In row 1 I want to have the names of servers, so we would have A C D E
Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.
Then B would have under it Start, End, Data, Time
And so on for C D ...
I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be
A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.
I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.
I am trying to find min value for a row (product price) within two columns (price lists) and return row one (company name) in third column ( see the attachmet). My first idea was to use min and if but a problem occur when there are text or zero or none values in the price list. I tried with conditional formatting, offset function, match-index and other methods without success
The code by Jindon works but only for the text files that do not contain a header. Can someone please telll me what I have to change in the code to make the code skip the header information (1st three line) when importing? Currently, if I use Jindon's code, it fails once it tries to import one of these text files. Here is Jindo's Sub test() Dim myDir As String, fn As String, txt As String, x myDir = "c: est" '<- change to actual folder path fn = Dir(myDir & "*.txt") Do While fn <> "" txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll x = Application.Transpose( Split(txt, vbCrLf)) Sheets(1). Range("a" & Rows.Count).End(xlUp)(2).Resize(UBound(x,1)).Value = x fn = Dir() Loop End Sub
I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:
1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.
2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.
The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....
