I am looking at combining 2 formulas and have them in a single cell. Currently I am using this one =IFERROR(IF(S3-V3=0,"Completed within agreed time!",S3-V3),"") if the value =0 but need another one if there's no value in V3 it would say "Outstanding" I have tried to add IFBLANK to the existing formula but I must be doing something wrong as it doesn't work.
View 1 RepliesI am trying to combine this formula with another formula but I cant get it right.
I have multiple worksheets that feed into 1 so I am using this formula to pull in the info.
=II.MissionCritical!C20. This info is a title like "Writing".
=II.MissionCritical!C23. This info is a yes or no.
So I need the =II.MissionCritical!C20 formula to also say if c23 is no add an *** to the front of the title. so the title would look like "***Writing". and if C23 is yes leave the title as is.
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View Relatedhow to combine these two codes into one?
Code:
Sub DelTemp()
Dim LR As Long, i As Long
Dim pt As PivotTable
[Code].....
I have the following formula... in Cell T4 =IF($Q4="",(M4&(IF(L4="","",(" ("&L4&")")))),(IF( ISNA(VLOOKUP($Q4, Lookup!$A$1:$B$5,2,FALSE)),Q4,VLOOKUP($Q4,Lookup!$A$1:$B$5,2,FALSE)))) But I also want to replace the 'R(space)' in the cell at the same time. I currently do that in a separate cell using: =SUBSTITUTE(T4,"R ",""). How do I combine the two..so that it is done in one action ??
View 2 Replies View RelatedIs it possible to combine the following three formulas into one formula?
=IF(AND(H5>59,(I5<5),(J5<15)),("Close Out"),(""))
=IF(AND(H5>59,(I5<15),(J5<5)),("Close Out"),(""))
=IF(AND(H5>55,(I5<10),(J5<3)),("Close Out"),(""))
I'm working on a project that spans three sheets. A2:A151 of Sheet 1 and 2 is a list of names. The names may repeat and they probably won't fill all 150 rows. What I'd like to do with formulas is combine Sheet 1 and 2 into a single list in Column A of Sheet 3 without repeating names or showing blanks. I'm fine with multiple formulas and hiding columns, whatever needs to be done to make it work. Is this possible?
View 2 Replies View RelatedHow to combine these two formulas into one big If formula..
=IF(F2=G2,2)
=--(SUM(F2:G2)0)
F2....G2....H2
10....10.....2
5......5......2
[Code] .......
H2 is the resulting cell.
i was wondering if there was a way i can combine these two formulas into one large one
=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))
=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))
Here's my formula that gives me an answer of zero.
=IF(IF(L14="L",(LOOKUP(B14,data!Y1:Y14831,data!AO1:AO14831)),0),IF(L14="L+R",(LOOKUP(B14,data!Y1:Y14 831,data!AQ1:AQ14831)),0),0)
L14 is a dropdown box, selection is L or L+R
The L true result is 30 and L+R result is 60
It should be possible to combine these should it not?
I would do a screen shot but it's quite huge.
On Sheet 2 I am looking for a formula to find Dog1 in a cell on a different sheet and then to equal a cell 2 columns to the right of where ever it found Dog1. I cannot make it so that it simply equals a certain cell all of the time. It has to be able to move in sheet 1. For example sometimes Dog1 will be on row 5 and sometimes it will be on row 7. So the equation I am looking for needs to be able to work no matter what row it is on. I have tried a couple VLOOKUP equations but they are not working because like I said, Dog1 is not the only text in that cell.
View 3 Replies View RelatedI am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")
Here is a sample table with showing the data and result
4
5L
4,5L
5
6R
5,6R
5
7L
5,7L
6
10R
6,10R
6
3L
6,3L
I'm having trouble combining some values in cells. For example in cell A1 i have the value 372-25. In cell B1 i want to make A1 part of another value (it is an APN if you must know). So that cell would be 011-372-25-11. I would be adding 011 as a prefix to each cell and 11 as an ending. I'm sure there must be an easy way of doing this.
I have tried
=011-A1-11
="011-"A1"-11"
="011-A1-11"
I am trying to create one formula (in one cell) so that it performs the following three things:
1) Rounds any number to the nearest dollar
2) Makes the minimum dollar value $2.00
3) If another cell (G2) reaches $100.00 or more, I want it to revert to another cell's (B2) value.
So, in essence, I want to combine the following two things:
=MAX(2,ROUNDUP(F2,0))
AND
=IF(G2>="100",B2)
I do not know how to correctly order these so that it performs all three things I'm looking for it to do. I've posted this before, but was given an answer that created a circular reference and did not make the minimum value $2.00.
I am trying to combine multiple cells into 1 cell per row.
I would have "A" column empty, then combine "B", "C", and on into the "A" column.
Each cell value will be separated by a space or any special character I designate in the macro
I have attached an example which the output is separated by a space. Sheet1 has the original file and Sheet2 has the desired output.
I have the follwoing example. Basically, there is an "X" for each posession of Andrew (cells: B1, C2, D3, E4). What I'm trying to do, in another sheet, is centralize the below information in only one row for "Andrew", with an "X" under each of his posessions (all the 4 "X"-es are now in cells B1, C1, D1, E1).
CarPhoneHouseBoatAndrewXAndrewXAndrewXAndrewX
how can I combine the Formula =(SUM(A2:A100))/(SUM(B2:B100)) with setting a value of the cell D2?
What I want would look like this (in column C):
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=0
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=100
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=200
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=300
and so on.
The point is that the values A2 to A100 are dependent on the input value in D2.
I am using excel 2007 and have the following information:
Cell A1: L6212- (formatted as text)
Cell B1: 05.50 (formatted as custom number 00,00)
When I merge the 2 cells using the concatenate formula I end up with
Cell C1: L6212-5.5
What I want is L6212-05.50
Say I have a column of numbers. I'd like to be able to string them together in the following format: number1|number2|number3....
connect.xlsx
combining 2 rows that share 1 cell so that it is easier to format cells. For example: A4 and A5 have been combined with B4 and B5 to form 1 cell that has content. How can I convert this to a cell that only occupies A4?
View 1 Replies View RelatedI am trying to create one formula that can be copied to other cells to get varied results.
Here are the formulas
1. =IF('Calculated Rental Fees'!H4='Daily Rentals & Charges'!$B$28,'Daily Rentals & Charges'!$B$30,VLOOKUP('Daily Rentals & Charges'!$A$20,LATECHRGETBLE,2,FALSE)*C4
2. =IF(AND(I4>='Daily Rentals & Charges'!$A$36),I4='Daily Rentals & Charges'!$A$38),VLOOKUP('Daily Rentals & Charges'!$A$22,LATECHRGETBLE,2,FALSE)*C4)
Basically i am trying to calculate late fees for a rental company using the if and vlookup functions.
I am trying to use following string to find result into column F and unable to find reason for Error.
=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))
A
B
C
D
E
F
1
part
code
price
find part
find code
Result
2
x
11
5
y
12
#VALUE!
[Code] ........
I have 5 columns AS-AW. Column AS contains a letter (either A, B, C, D, E, F, or G) which is generated by a VLookup formula. Columns AT-AW contain numbers (from 1 to 6) generated by a conditional formula. I need column AX to combine column AS with the smallest of columns AT-AW. Is this possible?
View 9 Replies View RelatedI'm trying to switch my vlookups to something more dynamic. I keep adding columns to the data sheet (sheet1 for these purposes) and so I have to go back and update all of the other sheets that use the vlookup. (I'll just give an example as sheet2 for these purposes)
So what I have currently:
=VLOOKUP(A5,Data_All,4,FALSE)
Basically the 'A' column in sheet2 has an integer value, and so the vlookup takes that integer and looks up on sheet1 which row corresponds to that integer and then returns that string value.
What I want:
='AO_Cat_' & 'A5'
Basically I have named every cell in sheet1 and the particular value that I want to pull is named "AO_Cat_%name" with the last part being the integer value. I don't know if that make sense or not. For example, on sheet2, A5 might be '16' and so the value that I want to pull is "AO_Cat_16"
If I were to do this from the VBA side of things, then it'd be a simple range("AO_Cat_" & id_number) where id_number was the lookup value. However, I'd prefer to have this directly as a worksheet formula for simplicity purposes.
File I have attached or made a link for here Untitled-2.jpg
I am trying to make that amazon cell, all text to appear as one line like the second line foreign conversion fee i want it to appear like that. Rather then having to delete and modify 100's of rows like this.
I have about 20 choices I'd like a user to select from and have the text output to a single cell.
Just create a Data Validation List? Yeah, but that only allows you to select ONE of the items in the list -- I want to select as few as zero and as many as all 20, combining the resulting text into a single string - space or comma delimited.
My first thought was checkboxes, but all I can seem to figure out is how to get ONE of the results into the cell, meaning I'm better off with the Data Validation List because it's easier.
Especially since I need to repeat this for as many as 200 or 300 lines,
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")
But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.
A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,
What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
I have a data set that I'm trying to sort in order to be efficient for some of my team members.
Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.
Col A
123-45678-A-1
123-45678-A-10
123-45678-B-2
I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.
My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.
Col A
Col B
123-45678-
1
123-45678-
10
123-45678-
2
Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:
Columns("B:B").Select
Selection.NumberFormat = "00"
To produce:
Col A
Col B
123-45678-
01
123-45678-
10
123-45678-
02
Now all I need to do is rejoin these 2 columns before sorting. My current code is:
Sub Rejoin_Container_Number()
x = 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Do While Cells(x, 2).Value ""
Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value
[Code]...
As many have probably already guessed, this produces the following result:
Col A
123-45678-1
123-45678-10
123-45678-2
I'm looking for:
Col A
123-45678-01
123-45678-10
123-45678-02
**zeroes in BOLD for reference only, result does not need to be bold**