Multiple Outputs From An "IF" Statement
Aug 9, 2006
Attached is a sample of a ledger I am making for my small business. Under the ' books' column, having the output display nothing for a row with no entry was simple, but I can't figure out how to have three different displays for the 'bank' column. The idea of this ledger is that I can continuously balance my books by entering the check mark as transactions are posted. The thing is, I don't want to have the balance display all the way down the sheet when there are no entries in those rows.
View 9 Replies
ADVERTISEMENT
Apr 8, 2014
I 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))))
View 4 Replies
View Related
Feb 4, 2014
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?
View 5 Replies
View Related
Jun 2, 2008
I 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.
View 9 Replies
View Related
Sep 7, 2012
how to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.
Here is my code - it's ugly but it was working when I just needed it to move down one cell:
lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then
[Code].....
View 1 Replies
View Related
Aug 11, 2014
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))
View 7 Replies
View Related
Feb 6, 2009
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
View 2 Replies
View Related
Jul 5, 2009
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.
View 5 Replies
View Related
Feb 14, 2007
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 Related
Sep 11, 2012
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.
View 7 Replies
View Related
Nov 2, 2012
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] .....
View 4 Replies
View Related
Feb 1, 2014
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.
View 1 Replies
View Related
Feb 12, 2009
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: ...
View 14 Replies
View Related
Feb 12, 2009
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 Replies
View Related
Feb 12, 2013
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?
View 2 Replies
View Related
Feb 14, 2014
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))
View 7 Replies
View Related
Jun 23, 2014
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] ..........
View 1 Replies
View Related
May 7, 2007
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".
View 8 Replies
View Related
Dec 21, 2007
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.
View 2 Replies
View Related
Jul 10, 2014
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)
View 4 Replies
View Related
Feb 10, 2007
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 Related
Dec 13, 2009
I have a spreadsheet that I need some help with coming up with a multiple if statement(or maybe sumifs?) I don't know. Here is what I am wanting to do(In somewhat plain old english)
if c3:c43 = A9 add g4:43 than does the total = h3:43 if so display true else false
I have attached a copy of the spreadsheet.
View 14 Replies
View Related
Mar 7, 2014
I have the following columns:
Status (column C), New Status (Column D) and Date Sent (column M)
Status column (C) contains multiple stages of progress status (Being Reviewed, Filed Not, etc)
New Status (D) column is blank (this is where I will enter the formula)
Date Sent (M) has mostly blank cells but some with dates in them
I would like a formula in column D (New Status) which evaluates C (Status).
If C = "Filed Not" AND M is blank, then output "Filed Not - Not Sent"
If C = "Filed Not" AND M has a date in it, then output "Filed Not - Sent"
If C <>"Filed Not" then output whatever data exists in C
I can get each of these to work individually, however, I can't, for the life of me, get them to work into one equation.
View 7 Replies
View Related
Jan 13, 2009
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
View 2 Replies
View Related
Jul 28, 2009
I have a formula that looks at 5 columns. If any one of these 5 columns meets the specified criteria, it will "Fail" the row. What I want is, when the row is failed, to display the column that was failed. Well I have that part under control using the following formula.
View 2 Replies
View Related
Nov 11, 2009
My concern is in row 4 and 5 of the attached spreadsheet. The problem is that not all postings for the allocations will take place on or before the date the allocation starts. Therefore, i need a logical formula that builds on what already exists and determines if the "entered" date is > the "start date". and then catches up how far it was behind. Example:
contract 12,000
Contract starts January 1st and ends December 31 (12 Months)
Entered Feb 1
In the January column there chould be zero in feb there should be 2,000 and the rest of the months should be 1,000
View 4 Replies
View Related
Dec 7, 2009
I want to say, if B1 = A then the value is 10, if B1 = B then the value is 20, if C then the value is 30 and D the value is 40.
If there are two conditions then I know it would be =if(b1=A,10,0) but this is obviously a bit more complex.
View 6 Replies
View Related
May 24, 2006
I would like to be able to search a group of cells for particular text,
placing the true of false value in another cell, using this formula;
=IF(ISNUMBER(SEARCH("ACP",A2)),"ACP","XXX")
My reason for this is that I have a large amount of data that I need to
break down. I have a column that is listed similar to below;
OR/ACP
OM/ACT
OR/MTS
O/O
The part before the / is 'system code'
The part after the / is 'module code'
I want to be able to have one additional column for system code and one
additional column for module code, without having to manually go through all
the data and make any amendments to it.
So as a result, (without the correct syntax) with 'S' being the system
Column and 'M' being the module column, what I would like to achieve is;
If cell contains "O/" then S1 = Office but if cell contains "OR/" then S1 =
Office Range but if cell contains "OM/" then S1 = Office Medium
I hope that makes sense.
Obviously repeated for the module column;
If cell contains "/ACP" then M1 = ACP but if cell contains "/ACT" then M1 =
ACT but if cell contains "/O" then M1 = O
View 9 Replies
View Related
Jun 9, 2009
I have in cell c40 a data validation list with source equal Royal (=Royal) has been created.
Royal is a namebox that has a defined list of 7 options (6 actual options plus one that says "select via drop down") :
FA4 = Select via Drop Down
FA5 = option 1
FA6 = option 2
FA7 = option 3
FA8 = option 4
FA9 = option 5
FA10 = option 6..............
The concept is that FA5 is associated with a value defined in FB5, FA6 is associated with FB6, so on
What I want to happen is when I select a value via the drop down selection in C40 eg. "Option 1" that in cell D40 the value associated with Option 1 (found in FA5) returns the value found in FB5 ($10).
How do I do this? I have tried a number of "if" statements but no luck.
View 2 Replies
View Related
Jun 30, 2014
I was wondering if there is a more beautiful way of this formula
Code:
{=MAX(IF(IF(B14=A1:A12, B1:B12)>IF(B14=A1:A12, C1:C12),IF(B14=A1:A12, B1:B12),IF(B14=A1:A12, C1:C12)))}
A
B
C
1
A
10
10
[Code] ...
Highest B is 80
View 7 Replies
View Related
