i have list of users with name, company, division, subdivision, date ...
the list can contain more than 10000 users and i want to be able to switch 2 users using a listbox/filter/ validation list.
there can be multiple users with the same name even company so a simple validation list which is limited to 1 column will not suffice. the interface for the switch should be fool proof so anyone can switch 2 users.
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
I want is to happen is that depending on which option a user selects from the drop down box (a total of 10 options), a select number of rows and columns should be hidden/visible (as you'll see from the below code..)
The other issue I'm having is that with the below code, the workbook seems to be very "busy" constantly calculating something or the other. I need the Sub to be run ONLY when a user changes a selection in the combo box, but it appears that every few seconds the workbook will be calculating something.
Finally, I introduced the GoTo Endo line after every option (1 to 10) to try and speed up the Sub when a user makes a selection, but this appears to make no differnce - it still takes forever to run the Sub.
I see where I can use the following to bring up the color pallet
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred
when copy/pasting, but IS if the keyword uses "quotes".
Sub MyColors()
' brings up the colors dialog
Application.Dialogs.Item(xlDialogColorPalette).Show
End Sub
But I don't know where to go from here. I would simply like to have cells selected, run the macro, select a color from the dialog, press the ok button, and have the selected cells colored per the color selected in the dialog. (Of course, pressing cancel would exit without changing any colors.)
The code below opens a window which allows the user to select a file to open. Apparently, it starts in the directory of the file containing the macro. How would I modify this to open in "M:Archived PO ResponsesProcessed"?
View 3 Replies View RelatedUsing the Roy Cox Database code http://www.excel-it.com/vba_examples.htm , I am trying to populate a user form when the user selects a list row. I don't know if this is important, but I have extended my list beyond 10 columns. The list appears in UserForm2, and it should populate UserForm1. UserForm1 is also available to fill in from scratch; the info pulled from UserForm2 is from a search.
Here is my problem:
UserForm1 does not populate with the current selection, but rather the previous selection. It will appear blank upon the first selection. If I close UserForm2 after getting a blank on Form1 and call UserForm1 from its own macro, I will also see that previous selection.
I'm trying to create a spreadsheet that will automate our job bidding process. We have alot of standardized phrases we use and I thought it would be nice to just click on the phrases that are pertinent to the user's current bid. How do I get the user's current selection (from a form, not a cell) to append itself to the "current" end of the bid (a worksheet)? In other words, previously entered information by the user (name, address, type of bid, etc.) is already updated on the new worksheet created by the new bid, but how do I get the user's current selections to find the end of what is already there, and add itself to the bid at that point?
View 9 Replies View RelatedI have a spreadsheet that has a long list of charges... some of those charges have multiple codes attached to them... when this happens I am wanting to group them together and let the user pick which one they want to view at one time.
Here is a very small example of what exactly I am referring to... with charge #14 I am wanting it to combine and let the user pick between 2565 and 2566. The problem is that the values can range and will not always be 2565 & 2566 and there is 10,000 + records similar to this.
test.xlsx
I would like to be able to limit a drop down list's contents based on a user's selection in a different drop down. I am using Excel 2003, and I know how to do this in Access, but I can't figure it out in Excel. Basically, I have a list of themes in one drop down. The user picks a theme, and goes to a second drop down which is a list of sub-themes attributable to that one theme and no other theme.
View 1 Replies View Relatedfind the attached workbook
I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:
When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.
Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.
I have a spreadsheet that lists users plus 4 columns of data horizontally and other data vertically. See image forum_sheet.png
My problem is the sheet is getting wider and wider as users are added. What I would like to be able to do is have a dropdown or something that allows me to select a users name which would then display their data set [ four columns ] on its own. I have tried validating a dropdown list but this only works on a single column.
See image forum_sheet_2.png
What's the best way to look at two sets of data in excel and make comparisons between them. For instance how many matches (and what are the matches). Workbook attached as an example.
matches between data.xlsx
So I have two sets of data, I've attached a worksheet which has them both. The datasets have different intervals, but I need to drag "Colour" from Data2 into Data1 and associate it with the correct interval. Sometimes the intervals match up and sometimes they don't - which could be a problem, but as long as its within a small spread it's fine. The "Colour" column in Data1 is filled with my desired result, the source being Data2.
ManyToMany_Example.xlsx
I have several data sets taken from an instrument recording the same sample. The data are noisy so I would like to average them. Annoyingly, the operating software for the instrument allows you to specify start and end values, but not the amount of points in between (unbelievable, I know). I therefore have data which looks like:
25, 1
25.1, 5
25.6, 7
25.8, 10
25.9, 12
and
25.1, 1
25.4, 4
25.7, 6
25.9, 14
So each replicate has a different sampling rate (the data set is much larger than the example above though). Is there any built-in Excel function to standardize these data to a fixed amount of x values, and perhaps averaging both the X and Y values of all points within the bin?
I have a large set of data in an array of many cells. I need to subtract a fixed amount from each of these cells without changing the location of the cells as there are many different references too them Is there a way to do this?
View 3 Replies View RelatedI own a custom print shop and I am trying to generate skus based on variables..Column A will only have one item (the product code), column B will have the sizes, Column C may contain more or less colors depending on what is offered for the shirt style, Column D hopefully can contain all the combinations. All this data will be in contained within one worksheet..
Column A
Column B
Column C
Column D
G200
S
Red
[Code] .......
I'm trying to work out a way to use a MAX function and IF Function to determine how i can find the highest number since a trade began. For example, i have a countif function that is identifying each trade (Column B) so what i want to do is to find the highest number for each trade (Column A) which is represented by dummy variable 1,2,3,4 etc. Please see example below: what i want to do is write code which runs for 3500 lines and finds the highest price since trade began and trade's are represented by 1,2,3,4,5,6,7 etc.
Close
Number
TRADE PRICE
HIGH PRICE
0.6347
1
$0.63
0.6565
0.6373
1
$0.63
[Code] ........
If I have ten numbers which I would like to split into 5 buckets (1,2,3,4,5) how can I best achieve this?
A trivial example:-
The series 12, 12, 18, 18, 25, 25, 34, 34, 40, 40 would neatly spilt into 1, 1, 2, 2, 3, 3, 4, 4, 5, 5.
Similarly
The series 12, 13, 18, 19, 25, 27, 33, 34, 40, 41 would neatly spilt into 1, 1, 2, 2, 3, 3, 4, 4, 5, 5.
A non-trivial example
The series 12, 13, 40, 41, 42, 50, 70, 71, 89, 90, 91 I would like to be split into 1, 1, 2, 2, 2, 3, 4, 4, 5, 5, 5.
So groupings are based on how close consecutive numbers are to each other and there must be at least one value in each bucket (1,2,3,4,5). The number of data items can vary form 5 to, say 20 or so.
I have 2 datasets. 1 of them is 5103 lines and other one is 5121. They contain the samde data but as you can see one of the dataset have 18 more lines than the other. Because of this I can not copy a columns from one dataset to other because the values will not match with the correct ones because of the line differences. How can i compare for different rows or how can i copy at least the 5103 line correctly and than fill other 18 by hand ?
View 1 Replies View RelatedAll of the strings are in one column and are in General format. They contain a few different types of strings: some only numbers, some numbers separated by commas (which makes them NOT numbers according to ISNUMBER), some text only, some text and numbers (separated by comma), some are 3 numbers, each separated by comma, etc.
ldJBlt9.jpg
What I'm looking to do:I want each of these distinct types filtered out someway. I've already done ISNUMBER to filter out the values which are numbers only, no commas. Perfect.
For the next step, I'd like to filter out cells which have only numbers and commas. The comma itself makes them considered not numbers, so how do I add the condition that I'm looking for cells with digits and a comma? A wildcard?
Edit: I've found the following formula which can check if it contains a comma, but doesn't specify between digits or text. Just commas. =IF(ISNUMBER(FIND(",",A1)),"TRUE","FALSE")
I am collecting data the temperature of an animal, i am also collecting ambient temperature at the same time when i plot the two data sets there is an 89% Correlation so i know the animal temperature is affected by the ambient temperature. i want to be able to take this influence of ambient temperature out of the recorded temperature. I dont think its a linear relationship
View 5 Replies View Relatedindexing complete data sets for each entered value. see example to better understand what I am trying to do. I have been trying for weeks.
Steve
INDEX.xlsx
I have a list of people whose direct reports have either completed or not completed a task at work, and there are four tasks. The records look like this (I've removed the direct report's name, which is to the left):
Manager NameTask 1Task 2Task 3Task 4Mickey SmithCompleteCompleteCompleteCompleteDanny Ledbetter
IncompleteIncompleteCompleteCompleteDanny LedbetterCompleteCompleteCompleteIncompleteMickey SmithCompleteCompleteCompleteIncompleteMickey SmithCompleteCompleteCompleteIncompleteMickey SmithIncompleteCompleteCompleteIncompleteDanny LedbetterCompleteCompleteCompleteComplete
I want to pivot the data so that for each manager I can see the % who completed Task 1, the % who completed Task 2, etc. preferrably in a horizontal format.
So, I can get this easy enough for the first task:
Count of Task 1Column LabelsRow LabelsCompleteIncompleteGroup 1Danny Ledbetter67%33%Mickey Smith75%25%
However to the right of this I want the results for Task 2, 3, and 4. The best I can do is the following:
Column LabelsCompleteIncompleteRow LabelsCount of Task 1Count of Task 2Count of Task 1Count of Task 2Danny Ledbetter67%67%33%33%Mickey Smith75%75%25%25%Grand Total71%71%29%29%
The results for Task 2 are incorrect. I've tried % of Row, % of Parent Row, and all the other calculations, and can't seem to get it. I've also tried putting the fields in the Column Labels box, the Values Box, etc.
I can even get this if I put the SumValues field in the Row Labels, which yields a vertical result, but still, the values are incorrect (Mickey has 100% completion under Task 2):
Column LabelsRow LabelsCompleteIncompleteDanny LedbetterCount of Task 167%33%Count of Task 267%33%Mickey SmithCount of Task 175%25%Count of Task 275%25%Total Count of Task 171%29%Total Count of Task 271%29%
I have a large collection of datasets, sorted/grouped by rows. I need to perform some calculations against these SETS (Ave, Low, Median, etc.).
The number of member in each SET is variable (1 to NN), the number of SET Members is included in the data. Example:
Code:
3 Data Sets:
Row Set Members Price Ave
1 A 4 $1.00
2 A 4 $1.25
3 A 4 $1.50
4 A 4 $1.75
5 B 1 $4.00
6 C 3 $10.45
7 C 3 $14.50
8 C 3 $17.75
how to compute the AVE value for each SET above?
I am thinking if I can determine the FIRST Row number for each set, add the MemberQty as an ~offset to determine the LAST Row, then use an Indirect reference to compute the ave, something like:
=AVERAGE(INDIRECT("D"&A1&":D"&A1+C1)) The SET's Ave value should be added to EACH row in each SET.
I am struggling trying to point a formula like this to the First Row of the NEXT set.
My problem is that whilst dealing with large datasets, i'd like to be able to create a macro which will give me a pop up box to input different types of keys words to search within a dataset. Once these keys words have been found, i would like excel to highlight the entire line so it can be extracted into a new worksheet.
View 3 Replies View RelatedI am facing a problem with WorksheetFunction.Correl
I got information, that matching class properties of Worksheetfunction is impossible.
This is my first experience with macro's and I hardly know how to deal with this problem.I got some excel file from my friend and tried to do this in the same way.
Macro was supposed to calculate the Pearson's coefficient of correlation for orders from 1 to 100 of autocorrelation in order to calculate Q Box-Pierce statistic.Data is prices of stocks for some bank and correlation coefficient is calculated for simple returns on stocks.
with the following formula.
I need to find the median of Column C and Column H and then divide the median sum of Column C by the median sum of Column H. My data contains blanks so I obviously need to count cells with data only. I am trying to avoid hard coding any cells and am looking for a single formula.
I have two different data sets that I have plotted using XY(Scatter) w/ smooth lines. Is there a macro or sub that will calculate the volume or area between where the two data sets cross?
View 3 Replies View RelatedI am currently using Excel 2013 inside of Windows 7.
I have 3 different data sets and in each data set the only unique identifier is the card number. I currently have to manually sort each of these fields because of the different variables in each data set. My overall goal is to align every user across each row so I can verify whatever status I have for the Card # in the first data set is the same across the other data sets. Is it possible this process can be automated by using a VBA macro that sorts, aligns and leaves empty spots when needed. Once again the only unique identifier that matches across all data sets is the Card #.
User Name
Card #
Status
User Name
Card #
Status
User Names
Card #
Status
John Smith
12345
Active
John Smith
12345
Valid
John J. Smith
12345
In Use
[Code] ..........