I am typing a number into Cell B4 (ex. zza4582) this number is a serial number that will be in many differnt cells in column "A" on a different sheet "Template Paste". On each row with that serial number I need the information from "A-I" to be copied in the main sheet starting on row 10 and continuing on from row to row depending on how many times the serial number is found.
View 10 RepliesThis may be an obvious one, I have received a spreadsheet with cell references or references to values that I am not familiar with, is anyone able to shed some light on how to interpret these references or at least find out where their source is within the spreadsheet?
View 3 Replies View RelatedI am trying to use an absolute cell refernce on tab 2 that refernces tab 1.
When I insert a row on tab 1 (tab name is week 1)the refernce changes. For
example cell A4 the formula is ='week1'!$a$4
when I insert a row on the week 1 tab it changes tab 2 formula for cell A4 to
='week1'!$a$5
What can I do to make it not make that change but still be able to insert
rows?
I need the close% column to auto sort from highest to lowest so that I can see at a glance who the top sales person is. I have conditional formatting for the top three but I would rather them auto sort by close%, can anybody help me with this. I have attached the file,
View 9 Replies View RelatedI am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
On a workbook named "Main Book" I have my whole inventory list on this sheet.
totaling over 200,000 items with 4 colums per row
Material # - Description - Family # - Price
(seeing how there is only 65,536 rows I have this list across over 20 colums)
So what I want is to have a VLOOKUP referncing this workbook
example:
material # / description / price
H047071504 / plate 1" mt box / $650.29
H152369500 / burst disk 5000psi / $45.65
manual enter / auto fill / auto fill
In a different Workbook when I type the "Material #" I want the next column to automatically fill in with the information (Description/Price) referenced from "Main Book"
I have dates values in 3 columns.
A1 - Header - "Holidays in XXXX'
B1 - Header - "Holidays in YYYY'
C1 - Header - "Leaves by YOU"
A2:B11 have static dates consisting of 10 dates in each column.
C2:C11 - the user may enter any date at any point of time.
I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.
Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.
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: ...
I would like the tab at the bottom of the sheet to Automactially name = to cell C5
So if Cell C5 = "Ryan"
The tab at the bottom of the sheet will be named "Ryan"
In my excel workbook, I have a customer table and invoice sheet among many others.
As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.
Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.
For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes
Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:
Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No
There are only 3 columns, A, B, C and only 10 rows but I would like it to sort automaticaly. I enter data on other sheets and it is loaded automaticaly back to sheet 1 after some calculations are made. I would like the data on sheet 1 to sort automaticaly on column C keeping the rows of data intact.
View 14 Replies View RelatedI am trying to figure out how to auto sort information on a spreadsheet so that when update notes are added by date at the bottom of the list, they are automatically sorted (moved) to the top of the list so the latest update is shown first. I am pretty clueless when it comes to macros although I have given it some attempts after reading other posts without success. I am including a copy of the spreadsheet. The info that I need sorted starts at B43:E43 and goes to B104:E104. That same sort info would then be used for the similar info to the right of this one. For example everything on the line B43:E43 would move together.
View 11 Replies View RelatedI'm building a sales tracking spreadsheet and need to find a way to auto group certain rows based on their cell value. In my tracker I'd like to group by company name and be able to collapse it to look at the details.
Here's an example of my starting point and how I'd like the data to look like in the end.
Starting State:
Company Name contact name sales stage Forcast amount
----------------------------------------------------------------------------------------------
Company A david brown contracts 10,000
Company A John Smith Won 20,000
Company A D'Arcy Davis Canceled 10,000
Company B
Company B
Desired State:
Starting State:
Company Name contact name sales stage Forcast amount
----------------------------------------------------------------------------------------------
+Company A 30,000
+Company B
I have two problems:
1) Eliminating doubles when ranking
2) Auto-filtering based on cell values
The first problem is an issue with my formula I believe (I am using a RANK + COUNTIF-1) formula (Shown in tab #2, column L of the attached document)
The second problem will need a macro, but I am not very strong with VBA. In the attached document, I am looking to be able to filter the table in tab one based on the selections I have included at the top. I was able to filter the table based on # of IDs displayed using a formula. The second filter is looking to only show the values in the table that satisfy the condition that column E must match the filter chosen (cell C3).
For example, of the filter chosen in cell C3 is "Yes", I would like the table to rank and display only those rows with a "Yes" in column E.
Attached: Excel_Help1.xlsx
For some reason I have a worksheet that won't sort stuff by Autofilter any more. I built it as a customer database but for some reason today I just can't get the names to ascend or descend. there is something simple I can check for, I can post an empety sheet if needed but right now I can't really post a list of all my customers info.
View 5 Replies View RelatedIs it possible for a column of "sums" to auto sort from largest to smallest? I have a dashboard with 20 charts and I want them to show in the order of largest to smallest but its rolling data so it changes daily.
View 6 Replies View RelatedI'm trying to learn how to do auto sort. I Know how to do the custom manual sort but it is taking to long to do my spreadsheet every night. I love to play the greyhounds but to manually sort 18 columns a race with 15 races each night takes a while there has to be a easier way.
here a sample of what I'm trying to do. The columns are in two C-D E-F G-H I-J and so on. The rows here are 7-14 as you see I have to sort some high and some low. Is this possible to do a auto sort for each set of columns
C---- D----------E------F--------G------H--------I------J
BEST-------------AVG-------------EARLY-----------LATE
SPEED-----------SPEED------------SPEED----------SPEED
BOX--LOW-------BOX--LOW------BOX----HIGH------BOX--HIGH
1----31.3-------1----31.47----- 1-----87.55------1-------0
2----31.2-------2-----31.69------2---- 51.53------2-----(-1
3----31.85------3-----31.95------3-------0--------3-------0
[code].....
Need to add an auto sort code to the end of this worksheet module 2, so that after all the dates are figured, it will sort by this date, no matter how many dates there are.
View 5 Replies View RelatedAs you can see from the attached spreadsheet. I have a page called Pattern Etch where we enter the data for all the processing done in a single machine.
Each device type is slightly different, and therefore has a different calculation fasctor for its etch time. This all weorks fine. What I had previously was another spreadsheet with the pages b and g in it. This is where the factors are calculated and updated from.
I'd like to make this update automatic, and suspect the easiest way is to combine the two spreadsheets, as in the attached version. Can the sorting of the data from the patternetch page to the apprpriate pages, b and g be done automatically? If so can it be done for many pages as I have cut down the spreadsheet, I actually have 50 variants, each will need its own page (as for b and g).
is it possible to auto sort data using VB script?
as i know i can manually do it but i would like to run a script to sort data on sheet 2
I have created a spreadsheet using columns A to G with row one as the following headings Surname, Middle Name, First Name, Alias, Offences, Temp# and CRO#.
The thing is I tried writing a VBA code to let the Surname(column A) automatically sort in ascending order but it doesn't work.
where sorting begins in Column A with the range A2:A5000 and at the same time row one being frozen.
I have the below small code which auto sorts information in column A & B but I am having trouble trying to get it to sort from row 3 and not sort the first 2 rows. The first row is the column header the second row I want left blank and then sort after that.
Plus at the moment after I enter something into column A it auto sorts itself but what I would really like to do is have it commence the auto sort after I have entered information in column B and pressed "enter". Because at the moment I enter info in column A then it auto sorts itself then I must then find it and then enter the info into column B.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Columns("A") 'Column containing names to sort
If Intersect(Target, rg) Is Nothing Then Exit Sub
Range("A:B").Sort key1:=rg.Cells(1, 1), order1:=xlAscending, header:=xlGuess
End Sub
I have a worksheet in Excel which contains scores for a quiz I am organizing. The rows contain the team names, the columns the scores per round per team.
I would like to have the worksheet automatically sort the columns in descending order (so that the winner will come out on top after the quiz has finished), everytime I change something (everytime a score is updated). I can't find anything in Excel which would allow me to do that, so I'm guessing it's only possible in VBA? Unfortunately, I don't know anything about VBA, so I'm stuck.
These are the ranges I would like to have sorted, in order of priority:
Row 1 is the row with the column titles (Round 1, Round 2,...).
C2: C26
N2:N26
M2:M26
L2:L26
K2:K26
J2:J26
I2:I26
H2:H26
G2:G26
F2:F26
E2:E26
D2:D26
I know that I could just click sort, decending, "points" etc but I figure there must be a way of it auto updating.....
Please see the attached file - I have attempted doing it with Ranking Formula but forgot that it would just give me error messages when two have the same points so I got rid of that.
Have tried doing it with other formula found on this website and others, however I appear to be stuck. I might be doing it wrong but as I'm not manually putting in the "points", they are being worked out with =( SUMPRODUCT((H4:H16={"W","D","L"})*({3,1,0}))+58) for example, it does not move their position.
What I would like it to do:
1. People select from drop down list whether they think the team will W, D, L
2. The Table to the right should automatically change the points dependant on the result
3. The Teams position should change automatically in line with their new points total.
I've written a fantasy football programme, however i'm having trouble getting the table to auto sort. alter one of the values in the wins tab, then goto the scores tab to see that they don't auto sort.
View 5 Replies View RelatedI am trying to create a VBA macro that autosorts a selection of columns by column A. I currently have the below already written which gives the desired effect. I was just wondering if there was a way i could have this script constantly running on the sheet so it is always sorted correctly without me having to click Run Macro everytime.
Sub Macro1()
Columns("A:F").Select
Selection.Sort Key1:= Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
I need to send an email when value of timer in column F exceeds 72 hrs. E-mail address of recipient is in column C and 2 fixed addresses are to be put in C.C.
basic outline would be
name(A) work(B) E-Mail(C) Work Started (D) Work Finished(E) Time Elapsed for Work from Start (F)
Can this emails be sent to repeat also after 24 hrs if the timer in F is not stopped (i.e till no entry is put into Column E)?
I got an excel, with a "validation list" in a certain cell.
Users can select value1, value2 or value3. based on this value, some columns will have to unhide (standard= hidden)
(only for value2 this is the case).
I used the worksheet_change event to determin the value, but so far I'm only getting it to hide...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If C24 = "value2" Then
Columns("H:O").Hidden = False
Else
Columns("H:O").Hidden = True
End If
End Sub
I am creating a copy of a spreadsheet (table format) in a separate workbook using formulas that will update the copy as the original updates. I would like to auto hide the rows that have the value "Returned" in column G. I have columns A - G , rows 2 - 2000. The value "Returned" is the result of a simple = formula. Is there some way to accomplish this?
View 4 Replies View RelatedI've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually.
I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up.
I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line.
I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = 1 Then
Rows(Target.Row).RowHeight = 0
Else
End If
End Sub
Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped?
Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example?
Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
Cells.RowHeight = 12.27
Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0
End Sub