1- code that will use to show position (1st, 2nd, 3rd....) based on two criterion in the attached sheet
A-the Student must has 49.5%
B-the student must get a "PASS" remark
2- How can i auto sort the position in descending order in another spread sheet.
In this attached example, this is a portion of a actual work procedure. The yellow highlighted cells are the same work element within the master procedure but one is for one type of machine while the other is for same model but added options. This is a pattern throughout the entire worksheet. Basically 1 model with several different types of options. I don't want to have unnecessary elements in a work procedure.
So what I am trying to accomplish, I want to create a userform or dashboard which will allow selection of model #, model type #, then based off those selections have excel populate a worksheet with all correct procedures based off model type choice.
In this example there is 6 model variations, all share same basic procedure, but all have their own work procedure based on options, so my above approach will allow me to make changes to the master procedure only instead of changing 6 procedures.
I want to end up with is when I type text in any row of the Master sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (as per column O). For example row 5's contents from the Master sheet would duplicate into the sheet named Accommodation. In addition, should the details ever change in the Master, this would need to be reflected in the individual sheets also. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie row 5's contents to be placed into row 2 of the Accommodation sheet seeing as it's the first occurrence, and row 6's would go into row 3 etc.
View 2 Replies View RelatedI want to end up with is when I type text in any row of the 'Master' sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (referred to by text entered in column O). For example row 5's contents from the Master sheet would duplicate into the sheet named 'Accommodation' as O5 contains the word Accommodation.
In addition, should the details ever change in the Master, this would also need to be reflected in the individual sheets. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie if row 5 had the first occurrence of the word Accommodation in column O, the contents of that row would be placed into the next available first blank row of the Accommodation sheet, which in this case would be row 2 as the headings are on row 1.
I'm looking for a formula that will paste an entire row from sheet 2 into sheet 1 of the same workbook when only 1 cell is typed.
I am trying to come up with a property access log for my guards so when they enter a tag number from(sheet 2 column B) into (sheet1 column D) , it will auto fill sheet1 column C and E with the entries from sheet 2 column A and C.
I want to do the following -
Whenever I enter a value say "X" in Sheet1, ColumnA I want the value to be autofilled into sheet 2, ColumnA if and only if value does not exist in sheet2, Column A.
How to achieve this in Excel 2007 ?
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on.
from row 2 onwards the data has been populated for roughly 200 rows.
i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet.
my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'.
how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row?
i know this is possible i just dont know how to do it.
I am trying to auto populate one spreadsheet from a master sheet
What I am trying to do: I have a master sheet where information on 25 countries is entered. Sheet has multiple columns for data entry I have 25 additional identical spreadsheets that correspond to one of the 25 countries so can drill down to country specifics
From the master - is it possible to select a country from a drop down menu so when I start entering data on the master it auto populates the corresponding sheet by the country selected.
I have a master list. I want to create an excel sheet that will follows when I key in the code number according to the master list all the rest of the value in the master list will automatically populate accordingly into their respective sequence into the created excel sheet.
Eg.
Master list
A B C D
0001 Lucy 8 Walton primary
0002 Rachel 9 Dermouth primary
The created excel sheet
Code 0001
Name Lucy
Age 8
School Walton Primary
I want to key in the code 0001 and the rest of the value will auto populate from the master list into the created excel sheet. The master list data go in accordance row by row. That means the master list is listed in a sequence where all the related value for the particular code into one row only.
I am trying to create a transfer of data from one worksheet to another, where Column G is where new data will be entered. I have a macro to sort the data (works great!!), but it is just getting it from one worksheet to another without having formulas in the cells - which can be deleted by my co-workers - to pull the data from one sheet to another.
I do not know if this can be done automatically or not, or if info is updated on one worksheet vs another - if then it will transfer the text.
Example:
(A2)LAST NAME (B2)1ST NAME (C2)RES# (D2)HOUSE (E2)DOB (F2)JOB (G2)SPECIAL
(A3)DOE (B3)JOHN (C3)112233 (D3)1 A2 L (E3)10/10/75 (F3)YES (G3)DR APPT
There are over 300 entries - one row new record. The CommandButton1 works great that I have for compiling and deleting unused rows.
But now I need something to get the data to the blank worksheet to compile data from Column G without formulas in the cells. This was one I found online, tried to adjust it to fit what I need, but I cannot get it to work.
Sub CopyRow(ByRef Target As Range)
Dim DstEnd As Range
Dim DstRng As Range
[Code].....
I have a summary sheet with a numerous data sheets to the left of it in a single workbook. Every time I add a new data sheet I have to type it's name on the summary sheet. How can I automate this?
View 9 Replies View RelatedI have a continuously growing list of data that I need to sort onto another sheet automatically. The sorting data is always in column A but sometimes is not always in the same format; however the sorting data is always the first 6 digits of the data in the cell. When i add the new data to the first sheet I would like the data to be automatically sorted onto the second sheet with 1 row in between each group of data.
View 2 Replies View RelatedI have a database being exported to a spreadsheet with over 40 fields (columns) and 1000+ clients (rows). One of the fields (column C) incidates that referral source. I would like to copy & paste the list into sheet 1 and have 5 seperate sheets for each of the 5 different referral sources. So that when I go to sheet 2 the only thing I will see are the clients and their 40 associated fields that are referred (column C) by say "John Smith"
Is there a function or script that will search column C for "John Smith" and then fill in all the 40 fields on sheet 2.
A B
1 List 2 things that went well during the meeting
2 Election of Chair Election of Vice Chair
3 Powerpoint Presentation
4 Presentations were informative Everyone at the meeting took part in Q&A
5
6 Group discussed Gardens Upkeep We were mostly in agreement
7
For each meeting attendee (row) they will give their answer to the question (A1).
Some will write no answer, some 1 answer, some 2 answers.
I need to list these answers in another sheet on seperate rows, thus seperate cells, in one column. Without having a gap between each of the answers on that sheet.
I have been wracking my brains and came upon this site searching via that best loved search engine we all know.
I would be very greatful if anyone could help me here.
I'll be watching this closely, nothing else to do.
so I'll be giving quick replies.
We are needing to do a two tier sort in a protected spreadsheet. Is this possible?
The two columns that need to be sorted have protected cells between them.
If there a way to ad a button that will automatically do this sort for the user... as in can I add a button some how in one of the empty cells that will do it?
Here is the sheet. [URL] ..........
Looks like I need to sort column A thru Z, except W.
I have a dorm roster on one sheet and the other sheet is an Alpha Roster. I want it to automatically sort alphabetically (column B) . Any time I change the roster, the Alpha Roster sheet doesn't automatically sort, I have to hilight and re-sort it again.
I updated the file, there are 4 tabs at the bottom. 1st Floor, 2nd Floor, and 3rd Floor. I want to have all the names, room numbers, and phases (ph) in alphabetical order on the Alpha roster tab. And when ever I make changes to one of the Floors, it will automatically update it the Alpha Roster.
I have a list of movie titles A3-A9999(Infinity), their disc numbers in B3-B9999(Infinity). The titles being written in text, of course.
All I want to do, is have another list, in the same sheet in D3-D9999 that will be the same list as the original list that I input movies onto, except alphabetical and automatic from when I type in the movie title in the original list. When i just use "=A1, =A2, =A3 ~~~ =A999" in the D cells, then try to sort, it only sorts by the cell numbers, not by the actual text that I want it to.
Is there a way to do this? I know excel relatively well, but I'm definitely a noob compared to experts.
So I have started working for a medical supply company and sorting through the prices and item numbers of thousands of line items is difficult to do manually. We have a computer guy who can take two files and match certain columns up.
For example, I have a file with the Item Code and Our Costs, and a different file with Item Code and a different companies prices (so I know what prices to beat while maintaining a higher margin). Every time I need to do this sorting I need to go to my computer guy and give him the two files to merge them together so he can match the thousands of items of our competitors prices to line them up with our costs for the proper item.
Example is attached as an excel file, although it is a hundredth of the size of many files I work with.
merging and sorting files.xlsx
I have a sheet with over a thousand part numbers (lines of data) whereby they are uniquely colored.
I am looking for code to allow the Macro to Sort the lines by the color of the line, identical to the Excel provided function. However, in that one- it is tedious to add the levels for the multiple Colors going on and I would like the Macro to perform it all at once.
So, I have five unique colors (Maybe doesn't matter)that remain constant: Red-Color Index:3, Yellow-color Index:6, Lime-Color Index:43, Gold-Color Index:44, and Gray-50%- Color Index:16? (I think).
So- when I run the Macro I would like it to Sort the data and group all the Colored lines (Parts) in series (doesn't matter the color order). In other words if there are 50 part numbers red and 100 part numbers Lime then Rows 1-50 are all the Filtered Red parts and Rows 51-150 are all the Lime and so on for all the other Colors.
I have tried to use absolute references (perhaps I didn't do it right) but it didn't work. Below is the code that does work but I need to first make the sheet visible then activate it.
Sub Row_Sort(tmpsheet As String)
' Sort the first 4 columns of the tmpsheet sheet
Dim tmprng As Range
Dim OldActiveSheet As Worksheet
' Activate the desired workbook (in this case Linewkbk)
Workbooks(LineWkbk.Name).Activate
' Save old active sheet
Set OldActiveSheet = ActiveSheet.............
I have excel sheet which is having following format
sr no | bank | branch | chk no | amount | party | bill no
1 | combobox1 | combobox1 | 123456 | 12000 | combobox1 | 123456
2 | combobox2 | combobox2 | 12346 | 2000 | combobox2 | 123456
3 | combobox3 | combobox3 | 12345 | 900 | combobox3 | 123456
4 | combobox4 | combobox4 | 13456 | 7000 | combobox4 | 123456
5 | combobox5 | combobox5 | 3456 | 18000 | combobox5 | 123456
When i try to sort amount column it need to sort and change with corresponding row but it is not happening so what should i need to do for that ?
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
I'm creating a Master document that has static information on one Source page that feeds to other sheets: Source contains name, address, contact, etc., that feeds to all other pages, budget feeds to invoice sheet, which feeds to contract, etc. Is there a way to copy information from one budget sheet and then automatically insert lines in invoice sheet?
View 1 Replies View RelatedI am currently working with a very detailed cost sheet that I have created in excel. To keep this description short... I ran out of columns after 6 months and had to create a new tab for the next 6 months. Basically, I have copied and pasted the first six months to the new tab and QC'd all the formulas and all that.. What I am foreseeing happen, as I've seen this in the past, is this cost sheet is a dynamic thing that will require the addition of rows for more line items.
My question is this, is there a way to "link" the two tabs so that if I added a row to the one sheet, it will automatically add a row to the 'linked' sheet in the exact same position with the same formatting?
I have a macro that returns a lot of data and I need it to automatically resize the row in which the data is being returned. Here is an example of what I mean:
Function MyMacro()
ReturnString = "help" & chr(10) & "me"
MyMacro = ReturnString
' resize the row
' need to define "Location", based on the location of the cell in which the data is being returned
Rows(Location).EntireRow.AutoFit
End Function
Even if I specifically hardcode the location of the row to be resized in the macro, it still doesn't work. The cell has "Wrap Text" turned on.
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I create daily report for dispatch documents to our various branches. Now I am looking for formula (or formula is not possible then VBA code) that will auto update document number and dispatch date in "output" sheet.
Find attached sheet with desired output.
i have a works order form with 6 peoples names on. when they make a order i would like my sheet to auto sum there order. so i can see how much each person has spent. i have attached a sample.
View 3 Replies View RelatedI need to auto date every sheet/Tab and can we make Auto date on foot tab? Example: 1st.Apr to 30 Apr on TAB.
View 4 Replies View RelatedI have cut and paste some code provided by members of this great forum to insert a row in a second worksheet at the same row number when one is inserted in the active worksheet. e.g. If I insert a new line at Row 14 in worksheet "admin" I also get a new row at Row 14 in worksheet "report".
Code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Set sourcebook = ThisWorkbook
Set sourcesheet = sourcebook.Worksheets("admin")
Set targetbook = ThisWorkbook
Set targetsheet = targetbook.Worksheets("report")
myRow = ActiveCell.Row
targetsheet.Activate
ActiveSheet.Rows(myRow).EntireRow.Insert
sourcesheet.Activate
End Sub
However, anything I do in worksheet "admin" triggers a new row to be inserted in "report".... If I change text in any cell, or make any changes at all, I get a new row in "report".
Is there a way to restrict this action to only a line insert?