I have a user form into which data can be entered and is populated on the next sheet. The order ID is unique, is there way I can make the Order ID auto generated each time a record is entered instead of manually keying the number?
View 4 Repliesi want to generate sheet automatically from existing sheet applying auto filter on specific column. i attached the sample which 3000 rows actually i have more than one lac rows so i have to put filter on sub_div column and then copy and paste to another sheet and give the name of sheet like F21. i want to do automatically this provide vba code or function for this
View 3 Replies View Related[URL]
I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.
How can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
Workbook1.xlsx
Is there a way to auto generate passwords? I would like the result to start off with the word "cook" and end in numbers. Is this possible?
Password: cook###
I have a spreadsheet that has thousands of rows, but they only want every 200th row to be populated with said data values.
Is there a way that I can auto-generate those values separately?
In the one Attached, I don't have thousands of rows.
question.xlsx
I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).
There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending.
I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.
The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)
I don't want to create a function to use within a cell, I need the Sub to run through a user form. I have a Userform to create a new account, and need to add a 6 digit unique identifier after a predetermined prefix ("T" for toddler, "Y" for youth, and "A" for adult). The constraints are that it cannot be duplicated (this is for multiple children registered within the company). I have tried using the GUID but having difficulty stripping characters and adding the prefix (this was my fix inclination).
View 8 Replies View Relatedif it was possible to have Excel sort and then Auto generate a work sheet.
Once a week at work I get a report that is split up via our 3 digit office codes.
Once a week I manually sort the info and split the original sheet into separate sheets based on the office code and mail it to them.
Its is simple and repetitive but takes me half a day to do due to the size.
VBA that autogenerates a unique number when a cell in excel is filled?
View 5 Replies View RelatedI need to auto generate and populate some cells with todays date but three years from now.
View 3 Replies View RelatedI facing a problem to generate request id number. Actually i need to generate id like example "RQ1013-01" where "RQ" is constant word, "1013" is month and year while "-01"is generated number. and every month i want the id number start from -00 back. thus in a month there is only 99 request is available.
View 6 Replies View Relatedafter spending hours searching the forum, I begin to seriously doubt my ability to even formulate a suitable search question. My problem SHOULD be a known one, but I am unable to find any clues. Here we go:
I have made a pretty stright-forward forecasting model. It is used to calculate economics of a coal fuelled power plant (even though that shouldn't matter). The outcome is, among other things, a single number for Profit / MWh in Euros. There are several input data, such as fuel price, price for CO2 emission rights, operational expenditure, capex, etc.
My idea, then, is to lock all in-data parameters except ONE, then let this variable vary incrementally and then produce a new small table with a) the variable data (example: price for CO2 rights ranging from 10 to 50 Euro in 2 euro steps), and b) the resulting net profit in each case. Ideally, I wish to be able to chose which indata to lock and which to vary, i.e. solution need to have certain flexibility.
NB: The calculations are not that simple, i.e. I cannot use a simple formula - I need to use the exising output cell(s) to feed data.
How do I do this?
The outcome should be basically two rows with a suitable number of columns (or the other way around, two columns in X number of rows). The table will then be used to produce illustrative graphs.
I guess I could hard code a table by simply enter data, but heck, that's not the way to use Excel!
way for my engineers to save a field ticket with a certain name based on data from a couple of cells in the worksheet. Re: Auto generate "Save As" filename from text and tried to use some code posted in the thread, but I an still not having any luck.
What I want to do is create an active X button when clicked on, would save the workbook to a certain folder. I want the name to look like this:
SO1!M3_SO1!M6_SO1!H2.xls
This is what I have so far:
Private Sub SaveMe()
ThisWorkbook.SaveAs Filename:="C:usersdefaultdesktop" & Range("SO1!M3").Value & Format(Range("SO1!M3").Value, "text") & ".xls"
End Sub
Would I click "general" or "workbook" in VBA when I enter this code?
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".
Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
Sub FilterUniqueData()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)
On Error Resume Next
With Worksheets("Sheet1")
[Code]....
The vba code below copies the selected value to cell C5 whenever the combobox is selected
Copy the code into a standard module.Assign this macro to the combobox
Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value)
End With
End Sub
Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
Call FilterUniqueData
End If
End Sub
My problem is this:
Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,
I have an Excel database of department contracts with a Master worksheet. I have created a worksheet tab for each of the departments. Column G has a dropdown list for each department. When I enter a new contract onto the Master I want it to auto copy to the coordinating worksheet based on the selected department.
If possible I would also like it to enter the newest contract would enter into the coordinating Contract Party entered into column D. (ie... If Hospital A is entered in column D on master, the new entry on the worksheet being copied to will be entered under the last Hospital A, if Hospital B is entered on Master it copies under Hospital B). If this isn't possible then I am not too worried about that part.
I have attached a blank version of the file. Current Master 5-15-14.xlsx
find the attached, I've some formulas on cells m,n & o and they related to each other, i just need n & o should be zero if m is zero.but the same formulas
should exist also.
Maybe this problem is not too much complicated for some of you experts. I have this:
I have in sheet "Products", in column A, a lists of Products from row 2 to N. (Product 1 , Product 2, Product 15, Product 37, etc).
How to generate in sheet "Menu" a matrix of buttons with the label of each button taken from the value of each cell in column A of sheet1.
I mean, the buttons would have the label Product 1 , Product 2, Product 15, Product 37, etc. Each button associated with macro "My_Macro".
I would like that the buttons be organize as simetrical as possible (regarding number of rows and columns)
* I'm attaching sample file with input in sheet "Products" and output desired for those few products. The real list could have 100 Products.
I want to randomize a range of values stored in sheet 1 and insert them in another cell.
I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50
the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba.
Monthly I am sent a spreadsheet detailing cost centre approval ranges for our management team. Unfortunately, the data provided shows a low range and high range in two different columns. Is their a formula to automatically generate the values for the missing numbers
For example
columns A
1001
&
columns B 1007
Can excel automatically generate the 1002, 1003, 1004, 1005 & 1006
I'm trying to use Advanced Filter to generate all the unique values within a list, only at times I want to Autofilter the data, then use the advanced filter to determine all the values of the slimmed list. For some reason, this works fine on column A, only returning the values that begin with a 1 when the column is filtered for all values beginning with 1. However, when I run the advanced filter on column 2 with column 1 filtered to all beginning with 1, all values all returned regardless of whether column 1 criteria is met. Either way, after the advanced filter, the autofilter has been removed.
View 7 Replies View RelatedHey everyone, I'm new to the boards and only moderately familiar with Excel, so I'm looking for a little help. My question might not be completely clear, so I'll try to give you a visualization.
I want to give users the ability to choose different kinds of expenses from a drop down menu (i.e., Salary, Postage, Supplies). However, some of the expenses have general costs associated with them on top of other costs, and I want it to be made clear by having the proper costs come up with the proper expense. For example: ...
I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.
I want to add & generate unique numbers in a column of similiar words..
For Instance:
RED
RED
RED
BLUE
BLUE
BLUE
etc..etc..
I want it to look like this:
RED1
RED2
RED3
RED4
RED5
BLUE1
BLUE2
BLUE3
I want infinite numbers next to the same wording but unique.
I have 2 rows A & B.. NAME & MOBILE PHONE Title in line 1
I am not to familiar with excel. Step by Step please, anyone out there.
I saw a similar posting, but the formula did not work out for me.
Then i am going to save the file as a CSV
Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
I need the easiest way to randomize or generate team numbers in a league. Using COL A assign numbers between 1 & 8 without duplicates. Then repeat 5 more times. This has to be done on the spot and has time constraints as the players will be waiting for their team assignments.
EXAMPLE: I have 48 players which will be assigned to 8 teams of 6. I want to randomize the drawing so the same players don't play on the same teams each week. Also to be able to adjust number of teams determined by how many players are present. either 6 , 8, or 10 teams.
In column B & C, starting at row 2, there are first names & surnames. In column H there is a grade for each name, eg B. Columns AC through AH hold subject codes for each name, eg 18E/Hs1 (potentially all columns could contain a code or only 1). The identifying part of the subject code is the first 2 letters after the / . So in the example the subject code 18E/Hs1 the 'Hs' signifies it is History. A table of subject code and their subject name is contained in a named range called Subject_ID (see below). I intend to make this range dynamic.
Subject .Subject
Code......Name
HsHistory
EnEnglish
ChChemistry
ITIT
PhPhilosophy
MpMaths (Pure)
PtPart-time
PyPhysics
SoSociology
BsBusiness Studies
SSSports Science
FrFrench
BiBuilding.
For each name (starting at row 2) I want to achieve the following: Scan across the range containing the subject codes (AC:AH), identify the first two letters after the / and match it to the subject name in the list. Paste the subject name to a cell starting at AI1 and then insert the grade (contained in column H) for that student in the corresponding subject column. The next unique subject name should then miss a column be pasted in AK1.
Example
......B ......C........H.. AC..........AD...........AE..........AF........AG.........AH.........
Matt........Pia......D....18A/Hs1...18B/En1....18B/Hs3
should result in the word History in AI1 and English in AK1 and the letter D in AI2 & AK2. Note as there is already a reference to History this is not repeated again.
I hope this is clear. I have enclosed a sample workbook with expected output and colour coded the subject names so that the order that they are pasted in is evident.
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
View 9 Replies View Relatedi had a basic spreadsheet for my incomings and i got wiped with my hd exploding!! i know how much the total was i just need a way of creating a list of random amounts within a specified range over a year to give to my accountant.