Im working a spredsheet for my work, it is a database of qualifications for certain employees. There are about a hundred different qualifications and about 20 different employees. And some of the qualifications overlap. I would like to input a certain date in one of the cells, and have it copy into some of the other cells where the quals overlap. But i would like to input that date into any of the cells that overlap, not just one.
View 12 RepliesI would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
> When the workbook is opened I want a box to appear with a message and 4 choices (as buttons?).
> Based on the button clicked I want 1 of 4 new boxes to appear (replacing the first box) and ask for input values.
>I then want the inputs to be written to specific cells on a specified worksheets inside the workbook.
Is it possible to have a code of say numbers or letter assigned to an explanation of that said code which automatically appears in the next cell?
See attached spreadsheet : Auto fill next cell with predetermined answer from list.xls
I have a simple spreadsheet of an inventory. Each row on this spreadsheet represents the data related to an item. I would like for a user to be able to INPUT a serial number via a userform. Then, I would like for the program to OUTPUT some information about that particular item (a few cells that should be on the same row as the serial number).
I attached an image diagram that may better represent what I am trying to do.
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I got a question where I cannot think of a good descriptive title for, I have seen this done but do not know how it is done. An internet user inputs data onto a web form, and and presses a submit button. The submitted input is 'processed' on a through excel and outputs the result back the internet user.
Two questions
1 - what is a good title for this?
2 - how is it done?
the actual output of the error message when I run my macro.
To repeat it, it says, "Regression - Having trouble offsetting input/output references"
The two button choices that appear on the bottom are "OK" or "Help." The OK button allows the macro to keep running (until it finds that it cannot find the regression output that was supposed to be created.
I have a long list of values, a few thousand lines, that I need to input into an array so I can run a formula on the values in the array and then take those results and put them into another array which I can then call to output down a different column. Is there a faster way to accomplish this than the typical For Loop with the activecell.value and activecell.offset commands, that just takes forever.
View 15 Replies View Relatedi have uploaded a file.....basically i have an input of names and i want to repeat these as shown in the output..is this possible.
View 5 Replies View RelatedI got all three dropdowns to be dependent on each other, now however, whenever I change the value in the top Input Dependent Box M39, I would like M43 & M47 to appear blank. I want these two cells to appear blank until you select the dropdown which will allow you to select your values of choice.
View 2 Replies View RelatedI want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:
Public Function xyz(a() as Variant) as Double
There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?
A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.
An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.
I am trying to write a match function to return the row of the value I search for. It is properly returning the value of the row I searched for. What I need to know is how do I get it to spit out information in the rest of the row. Here is my code so far:
Sub Rectangle2_Click()
Dim x As Long
Dim Row As Long
' Brings up input box
x = InputBox("Please Enter Tool Information", "Search")
'Exits if nothing is entered in the text box
'If x = "" Then Exit Sub
'MsgBox (SearchString)
'SearchString = x
MsgBox (x)
That gives me a Type mismatch error when I uncomment it out.
Something I was adviced of before in a situation like this way to Concatenate the columns, e.g
=A2&B2&C2&D2&E2&F2
in a far left column (insert 1 if needed).
That was supposed to be so that I can search through multiple columns. When I do that to test it, it never comes up with results. It always comes back as 0 meaning it wasn't found.
What I need is a script or formula with returns multiple results and puts them into one cell.
The Excel is used as a Project Managment Work Sheet for Resources, working on different Projects and their dedicated hours
The raw data table looks like this:
Column A: Project Name (=AllProjectsLists)
Column E: Resource Name (=MasterDataResources)
Column P: values from 0 to 8 (hours)
First I need to check per row the Resource Name. If the Resource Name is a match, than I need to check if in column P the value is bigger than 0. The result goes into a different sheet per row, one result per Resource, but the multiple results should be shown in one cell.
So the result should be something like:
Resource name "Thomas" .... Projects working on: "Project 1, Project 5, Project 13, ..." (in one cell)
Resource name "Mary" .... Projects working on: "Project 3, Project 9, Project 13, ..." (in one cell)
I tried with this one, but it only returns one vale per cell:
=SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1), ROW(1:1))
=INDEX(AllProjectsLists; SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1); ROW(1:1)))
I'm trying to use IF and AND functions to create a basic input-output system. The problem I have (more details below) is the 64 limit when the potential inputs could be infinite.
For values entered into a cell between 1 and 10, this should produce an output of 0.00 in another A value higher than 10 should output as 1.00, and from then an extra 1.00 is added for every '10' added. There is no limit on the input ie 1 = 0.00, 11 = 1.00, 29 = 2.00 and so on.
Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.
View 4 Replies View RelatedWhat I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required)
I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.
Have a person Input an Assembly Number and,Have Excel return a list of Part Numbers, their Descriptions, and a Quantity for each Part Number that will need to be packaged with the Assembly Number that was Input.
The output needs to be in the form of a check sheet (I can play with the formatting if I can get the information out) for the one Assembly Number input.The output can either be in a separate spreadsheet or a User Form. The spreadsheet is easier for me to work with but the User Form looks cooler.
I can use a VBA Function to Input the Assembly Number but then I need some way of searching a small table (about 20x20 cells) to find the matching Assembly Number and then return the Part Numbers, etc. to a check sheet so the parts can be packaged. Each Assembly Number can have several Part Numbers associated with it. Either unique to that Assembly Number or common to other Assembly Numbers. The Quantities can also be unique or common. A couple of simple examples might be:
Assembly Number - ANA0134
P/N Description Qty.
ANA0118 Widget thing 2
ANB0554 Thing Widget 4
Assembly Number - ANA0431
P/N Description Qty.
ANA0118 Widget thing 3
ANB0554 Thing Widget 4
ANC0023 Plain Widget 1
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
I have a list of data that I'm using the index - match function to output data based on user input.
For example, I have:
1 10
2 15
3 20
So when '1' is selected another cell outputs '10'.
However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.
I have a bit of code in sheet two of my document that requires user input to produce output. However I have the document set up in a vway that requires the actual input to be placed in a vell in sheet one, and the cell in sheet two is just "=cell in sheet one". This causes the input cell in sheet two to change but the output cell does not produce the new answer.
View 4 Replies View RelatedGot a road block on one of the trackers which I am trying to create which compares some of our agreements. Basically we need to compare current production with our contracts and then bring the tier level they are at in order to then calculate how much in overrides we receive. Belwo is an example:
Input Value = 140000
Out Should be = 1.5%
Set of Values 1
T1
T2
T3
T4
[Code]...
In a nut shell it will look for 14000 in set of values 1 and pick T3 to then go in set of values 2 and display corresponding T3 which will be 1.5% in above example as output.
I am trying to create a UserForm in Excel 2010 that will look for a part from our database (or on a specific Sheet, possibly a hidden sheet), then add specific data from that part's row into a new line.
More Specifically:
| Part # | Description | Category | SubCategory | Cost | Labor Hours | Weight | Etc |
I want to be able to input the Part #, and have it automatically add specified information to sheets 1 and 2 and those two differ. 1 is our Quote Sheet, and 2 is our bill of materials.
Also, I need each piece of information to go to a specific column (ex. Cost always goes to Column K)
What I have now is a broken UserForm that references a DOC file with a Table, but I need something a good but more complex.
Ideally, what I want is to have 4 ListBoxes where you choose Category --> SubCategory --> Make --> Model then have several CheckBoxes below that would determine whether to add a part (Cost, Labor, Weight).
The reason for this is because we have no need for the Cost or Labor Hours in our Bill of Materials, but need that information for our Quotes. And we generally don't need Specific Dimensions in our Quotes, but DO need them in the Bill of Materials...
Can I make it so that it will insert Pre-Specified information into both sheets at the same time?
Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
View 5 Replies View RelatedI'm trying to create an If then statement that will will allow multiple criteria and output the corresponding data if possible.
I've attached a sample data file to provide insight. I've also used a random number generator formula to create the numbers for the output. When I press F9 I'd like the data to appear in the dream house lottery section with the information of the house that corresponds the Neighborhood and street. What I have in mind below.
If C21=B2,B8,B13 and C22=B3,B4,B5,B9,B10,B11,B14,B15,B16, Then values of E,F,G,H,I appear in E,E,G,H,I appear in the lottery dream house section.
So =IF(C21=B8),IF(C22=B11), ????
IF(C21=2) and (C22=3), Then This is where my problem occurs. I'd like my output to be that of E through I11 to appear in E through I21.
Is that possible and does that make sense. I have a long winded version but it requires the formula to be in each and every cell and it will only allow for one neighborhood at a time.
Writing a formule to lookup for a value and return more than one result,
Example
Col A has Names, Col B has ID number, in Col C I need the output to look for names in col A and return the ID from Col B, I can use a VLOOKUP for this but the problem when col A has a name repeating more that once, In this serinario the lookup picks the first name and gives the first ID
Here is how I want the out put to be as:
Names ID Output
John 1 1,3,6
Mathew 2 2
John 3 1,3,6
Mike 4 4
Bob 5 5
John 6 1,3,6
The output should have the ID of all same names
I have been working on a little vba project and have almost got there with help from others, but the final part needs to have a tally a number of columns and and produce the tally results to a new worksheets - the code that need proof reading /reviewing is below - it is not working correctly as it is not tallying correctly and at times seems to miss counting the last row - so could you review and suggest or make required changes so that it will work on the attached test file.
WHAT IS NEEDED IS;
1. Column D contains names -strings which are repeated such Fred Flintstone so for Fred and others would like to have a summary tally of how many times each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U.
With the output summary worksheet called "WORKER TALLY" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
2. Then do same again for Column E - Names so for example the name Peter Pan and others would like to have a summary tally of how many each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U. With the output summary worksheet called "Names" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
Sub workertally()
Dim b() As Variant
Dim NewWs As Worksheet
Dim j As Integer, i As Integer
Dim a As Range, v As Range, r As Range, c As Range
j = 1
Set a = Range("E6", Range("E" & Rows.Count).End(xlUp))
Set c = Range("E5", Cells(5, Columns.Count).End(xlToLeft))
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
It would take forever to describe the problem so I just made a sample Excel file.
View 2 Replies View RelatedI am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
language
batch
WC
FC
zh
1
36
0
id
1
0
0
[code]....
in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.