Sort And Move Code, Create Equipment Lists`
Mar 5, 2007
I have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets.
On my 'final list' sheet I need the first 10 rows to be left out of the macro’s so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does.
I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.
View 4 Replies
ADVERTISEMENT
Jun 5, 2009
I have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.
I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.
The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.
View 9 Replies
View Related
Dec 17, 2012
I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
ie:
asset no
description
received by
day
[Code].....
View 2 Replies
View Related
Jan 13, 2014
Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
View 2 Replies
View Related
Jan 20, 2009
I've got a list of IP address in a column, between some others.
I basically need to copy the cell in a new column if the value match a criteria, and in an other column if not. e.g.
columnX new1 new2
a a b
b a b
a a
a a
a
b
it could be easy with a simple if condition, but that would create some empty cells.
So is anyone has an idea on how I could do that ? (I guess I could do it with VBA but I would prefer to find an easier solution as I'm not really confident with my VBA skills)
View 10 Replies
View Related
Apr 30, 2013
1) Imagine i have a row 1 with names, column 1 with bank account numbers, and under each name corresponding to each bank account it says "yes" or "no" depending on whether or not they are authorized on the bank account. How do i make 2 drop down lists, 1 with bank account numbers that produces the names which are authorized on the account and visa versa (list with names showing bank a/c numbers). It needs to be able to be updated easily.
2) Suppose I have the table as described above and another excel file with 1 column as bank a/c's and another column is authorized bank users. In this column however each cell just has a bunch of names for example one cell may contain (james, john, jack, jennifer, bill). Is there anyway to check these names against the ones in the table from the other spread sheet?
View 14 Replies
View Related
Aug 4, 2009
This is somewhat related to a post I had submitted previously but a bit more complex http://www.excelforum.com/showthread.php?p=2139259. I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, D1, or E1.
If the user "double clicks" on cell D1, I want to sort by column D using following custom list: AA1, AA2, AA3, A1, A2, A3, ZZ1, ZZ2, ZZ3, Z1, Z2, Z3. If the user "double clicks" on cell E1, I want to sort by column E using following custom list: AAA+, AAA, AAA-, AA+, AA, AA-, A+, A, A-, ZZZ+, ZZZ, ZZ+, ZZ-, Z+, Z, Z-
The issue I seem to be encountering is that I can’t use more than one custom list (e.g., if I use the column D sort list for column D, column E is also using the same list). I can’t seem to apply individual lists to each column. Would I need to “hardcode” the lists in my macro?
View 5 Replies
View Related
Aug 5, 2008
I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)
Detail:
I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.
The Chicago range is defined as:
=OFFSET(Sheet1!$A$2:$A$11,0,0, COUNTA(Sheet1!$A$2:$A$11),1)
The Seattle range is defined as:
=OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)
In the boxes on the right of the lists, I just have a Data Validation List formula as:
=Chicago and the other as =Seattle
View 4 Replies
View Related
Oct 7, 2008
I update a daily incident report that is sorted by priority of the request as well as the date/time. In the past, I've recorded and run macro's to add a number to a priority so that it can sort numerically. Lately I've been tasked with building similar functionality into more reports. I am wondering whether I can tell Excel what order to sort items into (ie "Sort by HIGH, then MEDIUM-HIGH, then MEDIUM, then LOW)?
With regards to auto-fill lists (for want of the proper name), I report on multiple IT systems that fall into six "system" categories. Is there a way I can set up the "System" column to automatically update the correct data once the IT service is entered, rather than manually look up systems category then click the relevant system in the drop down box?
View 4 Replies
View Related
Mar 13, 2008
Any way to create a list in a column from other lists in other columns?
View 9 Replies
View Related
Mar 24, 2007
Need the formula that I will need in my macro to move to the next column (up one letter) for the next time I run the macro witout me having to manually change column the letter?
View 15 Replies
View Related
Mar 13, 2009
I have a look up table on sheet 1, that looks like this table to the right. On sheet 2, I need it to find the table contents in the column CC, then look at a column AA and find all that match it and move all that match this cell in column AA and copy the row it to sheet 3.
Its 300 lines of data, a couple of time of week.
It is a sort of data with a twist.
AABBCCDDEETreeDogCathousecardavemanboyjobbaseTableboatsamlaurasamjimboydaveweedwalkwalkruncatfunlaughboydivefloatTreeDogCathousecardavemanboyjobbasedaveweedwalkwalkrunfunlaughboydivefloat
View 9 Replies
View Related
Jul 16, 2007
I am a buyer of hundreds of different types of items. I get thousands of different price quotes each day for hundreds of different items, 3 of which are balls, gloves and bats. I don't have the time to go line by line through thousands of quotes for hundreds of items each day to find the best deals. I want Excel to point the best deals out to me automatically, perhaps by making a cell.
Basically I need excel to sort my data by item type.
Then, calculate the average price for each item.
Then, point out any price quotes that are 10% below the average price, perhaps by making that quote in a red box. Attached is a spreadsheet with an example.
View 6 Replies
View Related
Sep 5, 2012
I need to create a list of all available variations from 2 lists. Example, I have a list of data that has 50 entries in it, and another list that has 300. I need to create a list of all options to import into a system that would include all 1500 options if you were to make a list of all possible combinations of the 2 lists of data...
EG. list 1 = 1, 2, 3, 4, 5 and list 2 = a b c d e
Final list would be
1a
2a
3a
4a
5a
1b
2b
3b
4b
5b
1c
2c
3c
4c
5c
etc...
View 1 Replies
View Related
Dec 16, 2013
I am creating a class list from a list of all students and classes. Names of students are in column A, their class name is in column B. In another sheet I would like to show a list of names from one particular class, say "Y8/En1".
Using a filter on the original list isn't an option as I will need it to pull/show other things eventually.
View 7 Replies
View Related
Oct 3, 2007
I have a number of text columns, side by side in a worksheet, containing names. I wish to combine these columns into one new column, removing any duplicates, to create a single list of names. Is there a simple way to do this?
View 3 Replies
View Related
Feb 10, 2014
I would like to know if there's a way to create a checkbox list in a cell that influences other cells:
For example, I would have column A as "Materials Accepted", and each cell would have a dropdown list with the options "Wood", "Drywall", and "Metal", in checkbox format. Then to the right of that, I would have each of the Materials as actual columns so that column B is "Wood", column C is "Drywall", and column D is "Metal".
The way I would want it to work from there is that whatever options I select from the dropdown, it would then put a TRUE flag in that column. So if I select "Wood" and "Metal" on the dropdown checkbox list, columns B and D would both read TRUE on that particular row.
Just wondering if something like this is possible, as I wanted to apply this concept to a much larger scale (About 29 columns for 29 Materials, scrolling left and right is really time consuming). So far I've only seen guides on how to create a SINGLE checkbox on a row, and none with multiple checkboxes that would function in this manner.
View 1 Replies
View Related
Sep 27, 2007
I have been trying to create a userform that will generate a numerical list starting in a given cell using a starting number, an ending number, and an increment that the user will imput into a textbox. I have a userform set up, but I do not know how to do any coding. I have very little experience with excel.
View 2 Replies
View Related
Mar 12, 2008
I have values in Worksheet 1, spread over A1:D25 and A200: D250.
In worksheet2 I have values again from A1:D25 and A200:D250.
Is is possible to only get the unique values of those 4 ranges with the advanced filter? They all need to be shown in eg worksheet 3 starting in A1, (so kinda merged in a sense)?
Is that a thing more for a UDF, or is there a excel function/option that does exactly that?
I have been looking for ages for that kind of function/option, since I thought it must be possible. But this sure does not look to be a standard functionality, or is it?
Is there a (free) add-in that might do this kind of thing?
I found this code on some office help page:
Sub SortAllRangeData()
' Place column header for temporary sort area.
Range("IV1").Value = "Numbers"
It kinda does what I needed, but it lists the actual data in the same spot it used to be. I want to be able to list the sorting in a different column on a different sheet and in 1 column only. Is this difficult to modify so it becomes a UDF or is this something totally different?
View 9 Replies
View Related
Sep 20, 2008
I have a simple projectlist on sheet1 formatted like this:
Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status.
On sheet2 I have the same columnheaders.
What I would like to do, is that if Status changes to "Done",
* the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything
* the now empty row on sheet1 should be deleted (so there're no empty rows)
* the data on sheet2, should be sorted with D as first key and C as second.
Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1.
My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour?
I only know small bits of VBA but my idea is this: ...
View 4 Replies
View Related
Apr 28, 2014
I expect to deploy over 200 iterations of a workbook (templates if you will) containing a mix of up to one hundred different attributes each with its own list of valid values. My problem is the attributes don't always default to the same column in every template workbook. Therefore, to make things easier for users I would like to create attribute valid value dropdown lists on the fly using the "Valid Values' table as the source data (see the attached). Since the attribute names will be the same from one template workbook to the next, I wanted to come up with a macro that would reference a source table (a separate workbook) containing all valid values for all attributes which users would be required to download to their local machine.
See the attached example workbooks and an example of a "Valid Values" list. Here's what needs to happen: When a user opens template workbook #1 I need drop down lists containing the valid values for the attributes contained in cell B3 (User Code),C3 (Department) and D3 (Category). Likewise, when another user opens template workbook #2, I need drop down lists containing the valid values in cell B2 (Department), C3 (Group), D3 (Category), E3 (Level), F3 (Material) and G3 (User Code). Notice that "Department", "Category" and "User Code" are data elements common between both workbooks but resident in different columns.
View 2 Replies
View Related
Sep 17, 2012
I have been working on a template using validation tool so as to create drop down lists. Now I have to select data from a combination of two fields.
Example: I need info from Dataset A and Dataset B to create a new drop down list from Dataset C.
I tried with If AND formula =IF(AND(B4="Test";B6="de10");"ok";"no") where due to the selected values I got Ok as a return answer. However I need to have a dropdown list of relevant values selected and I am having some issues.
When I name my group (Dataset C) as DE10 so that it can be linked to DE10 value (dataset B) so that I can do a validation then I get an error as DE10 is an excel field name; therefore I thought of the IF AND solution as I was thinking of doing so that I can name Dataset C as XYZ (instead of DE10) on the data sheet however I am having some trouble in getting the drop down list to work.
Basically I need a drop down list from dataset C when certain info is picked from Dataset A and Dataset B.
View 1 Replies
View Related
May 18, 2014
We have a proposal generation tool that we use that is based in Excel, and it works very well indeed. However, we're wanting to add a 'Custom Contract Generator' tab to the spreadsheet, and I'm at a loss with how to build it.
What it needs to do is :
- Allow the end user to select which contract clauses are required for the proposal they are working on, ideally via a range of dropdown boxes.
- Use that selection of clauses via dropdown box to create a compiled, formatted text list made up of those clauses.
- Enter that information into a landscape orientation Excel tab in such a manner as allows for tidy, business-suitable printing.
In short, I want the ability to pick what clauses we want to use, and have Excel generate a custom contract Terms and Conditions page based on my selection. And I've no idea where to start.
View 1 Replies
View Related
Oct 6, 2006
I need to create a scroll list where user can scroll the contents and see and select a perticular Item and whe he does it (selected) should display on the next cell
View 2 Replies
View Related
May 9, 2014
How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB
View 9 Replies
View Related
May 16, 2014
if there was a way to automatically add each student to a class list as their information is inputted. What i mean is, after input Sally Student in the main enrollment list she would automatically go to the class list for the class she has chosen.
I have attached a sample workbook with my desired results. Basically what i want to do is make this easier for those in charge to get the class lists from the main list without having to copy and past all the time.
The class lists are in the tabs in the workbook.
View 5 Replies
View Related
Jun 6, 2013
I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)
I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.
I've tried a few things to make it happen, but nothing has worked.
I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.
I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.
Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?
View 5 Replies
View Related
Nov 10, 2008
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View 4 Replies
View Related
Mar 20, 2009
I have attached a file where I’ve made some dependent validation lists. When the first box is changes it clears the last 2 with this bit of code
View 2 Replies
View Related
Jan 3, 2007
I've got a spreadsheet that lists multiple pieces of equipment and when it was worked on or down. It has the following columns:
Date
Jeep #
Start Time
Stop Time
Total Repair Time
Other Downtime (which is most often blank, as below)
7/18/06.......35.....1:00 PM.....2:00 PM.......1:00
7/20/06.......33.....1:00 PM.....2:00 PM.......1:00
7/28/06.......35.....9:00 AM....11:00 AM......2:00
10/18/06.....50.....6:30 AM.....7:00 AM.......0:30
10/18/06.....33.....9:00 AM.....11:00 AM.....2:00
(I hope the ... is okay, couldn't figure out how to make the columns line up.)
I can use Sheet Two for the next part, but I don't know how to to get a total time per month for each seperate jeep. Most of the time, there will only be one record per month per jeep, like the second line above. But lines one and three are for the same jeep, and so I need the formula to add column E up only for those two lines.
View 15 Replies
View Related
