I'm having a mental block with what should be some reasonably simple nested logic. Basically I've got 3 possible inputs in one field and 2 in another, and want to get 6 possible results out the other end. The sheet attached shows what i'm putting in (columns A and B) and what i'd like to get out the other end (C). The formula i need in column C is a bunch of AND's that i somehow keep getting wrong
View 5 RepliesI am looking to create an excel search with two input and three output values on Sheet 1 that will link to a table on Sheet 2. To help illustrate:
Sheet 1:
The two white cells indicate the two input values, and the three blue cells indicate the three desired output values.
Sheet 2:
I want to enter the two input values (Length and Width), press the "Search" button, and have the corresponding output values (DWG#, Item #, and Tooling #) from Sheet 2 appear in the three output cells on Sheet 1.
I'm trying to take 5 inputs that are all in text format and inputs have fixed options what i need is a macro that will take them and turn them in to the relevant outputs. there is a select amount of combinations and i've been asked to do each combination as a separate macro or sub macro.
What I have so far is;
If C6 = "Panel plate" And D6 = "24 by 21" And E6 = "<1.59" And F6 = "N/A " Then I6 = "120" And j6 = "4" And L6 = "120"
End Sub
A B C D E F G H I J K L
1
2
4
5
6 Panel plate 24 by 21 <1.59 N/A
7
8
9
10
(Doesn't seem to work)...
If those four cell match up then i need I6-L6 to line up with whats required.
If they don't equal that i need it to stay clear.
I also need to add the same macro with different options.
I have a small problem here with my worksheet. In my college i need to make a project about sales in a wooden floor shop.
I have done one part of work but actually i'm stuck at the specifications of my sheets.
What I need to do i added the screenshot with design Screenshot 2014-02-02 01.29.03.png
For my project i need to describe everything in my workbook as you can see in screenshot, i have made some notes but i'm note sure if its really right my teacher don't want or can't explain me well this part of work, i don't know why...
Now the question> i would ask some nice people if they can check my sheets and show me where i need to put inputs and outputs notes in the design sheet. Also in the screenshot I surrounded a part with red line there is a very strange thing no one know what is it 3 teacher telling different things.
How can excel find the least amount of inputs needed to produce a specified amount of outputs? To boil it down to the basics:
Need=
100 units of x
200 units of y
From a combination of=
item A that yields 25 units of x and 5 units of y
item B that yields 10 units of x and 15 units of y
Objective = minimize the number of items A & B used (i.e. minimum of (item A)*(item B))
Using Excel 2010.
I extracted data from .pdf to Excel using Able2Extract. Now I need to scrub the output a bit.
I see commonalities in the data for the start and stop of each set of data that I can key in on.
Once I find the start and stop points for each set of data I would like to fill all rows in-between the points and then discard anything that remains outside of these boundaries.
I have something wrong in my logic, way too many rows are deleted.
Code:
Option Explicit
Sub GetLineSets()
'Purpose: Identify relevant line sets, delete all other rows
Dim wbBook As Workbook
Dim wsData As Worksheet
Dim strFormula As String
Dim lngRows As Long
Dim C As Range
Dim blnFlag As Boolean
[Code] ......
Trying to write function statement that does the following:
IF cells C9:E14 contain any of the following: "Termination" or "Forms" or "PWE" or "TSA" or "3rd Party", then cell would show "Provided Date (If Applicable):",""
This is my best shot at trying to get it to work, but it wont, no matter what iteration I try.
=COUNT(IF(C9:E14="TERMINATION",IF(C9:E14="FORMS",IF(C9:E14="PWE",IF(C9:E14="TSA",IF(C9:E14="3RD PARTY")))))>0,"PROVIDE DATE (IF APPLICABLE):","")
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies View RelatedUsing VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
The issue: Theres about 300 rows of data arranged in multiple columns. One column has a dollar amount and another column has a code. Data gets entered into the sheet periodically so every new dollar amount needs to be transferred to another sheet by recognizing the right code.
What I want to do: I want excel to recognize the dollar amount and attribute it to the code in the same row. Then sum and transfer that amount to another sheet and if possible add it to an existing amount the in the correct cell.
The final outcome should be that any user of the document can just copy and paste data into one sheet and the document automatically generates the costs in another sheet. And if possible every dollar amount should be linked to a code so a user can just click on the amount and see where it came from and under what code it falls under.
I need a formula to create random letters down a column: A-F. Probability of A=3%, B=6%, C=8%, D=11%, E=14%, F=16%. I've got it worked out to where each letter is a header of a column ("A" is in A1, "B" is in B1, etc.). My question is: how do I consolidate that in one column, so that, "A" for example, reads ABFCBACFBFBECEABCEABCEAFBCEFBCAFEBCFAEBFC...? I'm using the random number generator for the 6 columns...thx
View 5 Replies View RelatedI need to multiply the result of the 3 if statements as shown below in one cell Each of these 3 if statements output an integer and I need to multiply the value of each of these 3 output integers to get the final output integer. syntax of multiplying if statements outputting an integer?
IF(ISNUMBER(SEARCH("Gold",B20)),C11,IF(ISNUMBER(SEARCH("Silver",B20)),B11,
IF(ISNUMBER(SEARCH("Platinum",B20)),D11,IF(ISNUMBER(SEARCH("Customized",B20)),E11))))
IF(ISNUMBER(SEARCH("Gold",B20)),C2,IF(ISNUMBER(SEARCH("Silver",B20)),B2,
IF(ISNUMBER(SEARCH("Platinum",B20)),D2,IF(ISNUMBER(SEARCH("Custom",B20)),E2))))
IF(ISNUMBER(SEARCH("Gold",B20)),C12,IF(ISNUMBER(SEARCH("Silver",B20)),B12,
IF(ISNUMBER(SEARCH("Platinum",B20)))),)),D12,IF(ISNUMBER(SEARCH("Customized",B20)),E12))))
On one of the reports which i am doing to identify the series of values for a single input data. In the attached spreadsheet there are 2 tables, table 1 has complete data with column a is the lookup value and column b is output value
I have to fill the table 2 in the same manner as shown in the attached template. there could be many different for a single lookup value but i am targeting to look for the 1st 9 matches and last matched value. eg. if i am looking for the value 9 which has 15 different values i want to display the 1st 9 matches (p,q,r,s,t,u,v,w,x and ad - being the 15th match)
Not sure whether this could be possible by using formulas?
I have a userform with 5 data fields. The first two are listboxes and the last 3 are textboxes. I have recently added the multiselect option to ONLY THE FIRST listbox (MeetingSpaceBox). I can get the multiselect listbox to output correctly. The problem is, though, that the rest of the data will only fill in the first row and will not be added to any of the subsequent rows. The data from the other 4 fields should remain constant for each selection in the first ListBox or at least until either "ok" or "clear" have been selected on the UserForm.
Do I assign all those valuables to variables and fill them in after the Multiselect listbox data has been added? I assume this will mean a change (or deletion) of the emptyRow code.
See my code below
VB:
Private Sub CalcButton_Click()
If AllDataEntered Then
'define empty row
emptyRow1 = WorksheetFunction.CountA(Range("A:A")) + 1
[Code] .....
I am in a fix on how to create a formula where the user can drive the outputs by selecting specific timeframe (placeholder Cell add: C3 on worksheet named Staff Number Calculation).
Based on this selection, the output should be calculated in address C9:H25, now where I am getting stuck is referencing as this output is based on values present in worksheet named "Volumes" and "Time Spent Per Unit".
However, as volumes vary by each month, the number od workers required should also be reflective of changing in volumes worked upon.
For example: ...
I have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?
I am working on a complicated macro. The macro is suppose to move certain data set to a specific worksheet if an if statement is "True."
As of now the macro, inserts the row into a new sheet but it is inserted into Row 1. My data set is in Sheet A is up to Column U and I want it to be inserted in Column B on Sheet B. The code below inserts the entire row into Sheet B starting with column a.
[Code] ........
This variation of the code moves the entire data set from Sheet A to Sheet B and inserts it in Column B as wanted, but it does NOT follow the Criteria of "true", it brings the entire data set even those with "False".
[Code] ..........
I have a textbox on a userform.
The textbox is populated with a number which is generated by a formula which is
the result of a "Select Case" module.
On some occasions, for instance when dividing 1000 by 3, I get a result with recurring decimals in this case 33.333333333.
Is it possible to make the "result" round to the nearest whole number?
The textbox is named "inDorW".
My goal is to match two outputs from a database so that the same person's record can be found on one row. At present output 2 does not marry up to output 1 due to staff leaving and new appointments being made. The data in output 1 should remain static. The data in output 2 must be manipulated to match the order of output 1.
Output 1 is a 5 column record containing (in A6:E6)
Forename, Surname, Branch, Roll Number, and a % score
Output 2 is again a 5 column record containing (in G6:K6)
Forename, Surname, Branch, Roll Number, and a % score
Each employee can be identified by a unique Roll Number Code contained in the fourth column of each record.
If an ideal match has been found I'd like the letter Y to be inserted in the L column of each record to signify a positive comparison. All non matches should have the letter N in the L column & these non matches should be placed at the bottom of the dataset.
Below there are all the information needed to understand my problem.
M(1 to R,1 to C): matrix with R rows and C columns [element known]
M(i,j): elements of the matrix M in position i(row)-j(column) [element known]n
Assuming I have three worksheets:
1) input: col A has a series of product code (string)
2) calculation: where I want to automatically copy and paste each product code from "input" worksheet into "calculation" cell B4, and through many steps of calculations, return the outputs in cell B8 and B9 (parameters are numeric)
3) output: how to automatically record B8 and B9 from "calculation" sheet to this sheet Row 5 and 6 from each loop? (format of output doesn't really matters)
The weather data I receive from a web query pulls data in oddly. For example, some days it pulls the weather for today into cell A1, and the forecasts for the following 3 days are in A4, A9, then A19. The cells in between are blank, and the problem is that it pulls it in randomly otherwise I could just have the cells reference it (ex. some days it pulls it into A2, A8, etc) What I need to do is arrange it, in order, without the blank rows, but I want to do this without deleting the rows.
View 4 Replies View RelatedThere are three situations that should yield the answer "overdue" and a fourth and fifth situation that should yield an answer "on target". I'm new to functions and have spent SEVERAL hours on this
{=IF((G4<>0,F4<$g$1="Overdue")+(F4<G4="Overdue")+(G4<>0,F4>$g$1="On Target")+(F4>=G4="On Target"))}
Although the "Trip Sequence" is same , but I need to separate the Trips if the difference between the "1ST TIME " is more than 3 hours keep the first "Trip Sequence" unchanged , then by either adding a prefix or add 2000000 to the second "Trip Sequence" and 3000000 to the Third, so forth so on until all the rules are applied ( assuming more than 3 Trip Sequences could be also allocated in the database ).
Data Base
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA3270004
XXX05111835$VV1984GVACDG3270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
In the above examples , The "Trip Sequence" is same 3270004 but the "1ST TIME" is more than 3 hours difference:-
I want to be able ( ONLY IF "1ST TIME" is in access of 3 hours while the Trip Sequence are same , I want the results as follows:-
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA5270004
XXX05111835$VV1984GVACDG6270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
I have 3 ranges in an excel sheet
col 1 range contains a numeric value ("1" or "5")
col 2 range contains a string value ("left" or "right")
col 3 range contains a string value ("a" or "b" or "c")
example: I am trying to get a total count if col 1 contains either a "1" AND
col 2 value is "left" And col 3 value is "a". Note: If col 1 is blank (" ") then the rest of the row is irrelevant. See attachment
I am trying to calculate the number of forecasted hours worked per week for each contractor based on their allocation to the project and also based on a 37.5 hour standard week.
I am using a formula which was previously provided to me for another problem but theoretically should share the same logic. The formula works only when a contractor is forecasted to work for a full week. If there is a contract which finishes mid week the formula returns a weird value. Also another problem is that if a contract is starting on the 12th of January, it still forecasts a full week for the week commencing the 7th of January. I have attached a spreadsheet for everyone's reference.
I want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
Sample spreadsheet attached to provide a visual of what I'm trying to accomplish.
Issue: Need to identify which userid's can be deployed and which userid's can't
Detail: In the attached spreadsheet, column A would list all userid's that the project team wants to deploy an application to. Let say for this example that they want to deploy Adobe Acrobat Pro v9 to replace Adobe Acrobat Pro v7. In my actual spreadsheet, there are 1307 userid's in Column A.
As a result of meetings, we have identified 11 Functional Teams that CANNOT have their users upgraded to Acrobat Pro v9. Using internal LDAP look up tools, I am able to generate a list of userid's for each of those 11 Functional Teams. The numbers vary. Again, in my actual spreadsheet so far, Functional Team #1 has 600 userid's, FT#2 has 2700 userID's, FT#3 has 6700 userid's, etc. I'm not done generating the userid's for each functional team but I know I need assistance once I'm done!! LOL!!
Using Column A, I'd like to see if that same userid exists anywhere in Columns B thru L. If it does, then that specific userid cannot be deployed. I would like that userid to be displayed in column N. And in Column O, I'd like to display the name or names of the functional team that this userid is a member of. It could be multiple teams. This will help me identify users that have multiple functional team memberships. Useful data for future deployments.
Using Column A, if a userid does not exist in Columns B thru L, then that userid can indeed be deployed. I'd like to get that userid displayed in Column M.
In the columns below B is just 95% of A. What I want to do in column C is to put the maximum value of B over the range that corresponds until the value in A is less than that. For example for my 1st run the value would be $1,125.68 because this is the maximum amount before you fall below that in column A, or in this case hits $1,106.40. I want to be able to perform this automatically down the line (expanding the range until the logic test is true) but can't figure out how to do this. The formula would basically be the max of the range in Column B is > Column A until this is not true, then return the max in Column B for entry in Column C. Then in Column D I would run an identical analysis but return the Row identifer for the dataset.
View 11 Replies View Related