i need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.
View 3 RepliesWhy than going line by line on a document that has 60k rows!
I have a document with NAMES in column A and ROLES in column I. If the user has more than one role..their name will be listed in "A" for each role in "I". I'm trying to purge down a file that has 60k + rows. Lets say i want to see all the people and all their roles if they are in "PROGRAMMER_ANALYST" role...can this be done by macros? or formula?
I have 2 worksheets (worksheet 2 is Slicing and Worksheet 1 is Production).
Production worksheet D6 has a dropdown box for, 1, 2, 3, 4, 5, 6, 7, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6 and 7.7.
In the Production worksheet, I have in cell E6 =IF(D6=1,"ONE",IF(D6=2,"TWO",IF(D6=3,"THREE",IF(D6=4,"FOUR",IF(D6=5,"FIVE",IF(D6=6,"SIX",IF(D6=7,"SEVEN")))))))
In the Production worksheet, I have in cell F6 =IF(D6=1.1,"ONE",IF(D6=2.2,"TWO",IF(D6=3.3,"THREE",IF(D6=4.4,"FOUR",IF(D6=5.5,"FIVE",IF(D6=6.6,"SIX",IF(D6=7.7,"SEVEN")))))))
How do I show in Slicing worksheet cell B6, either 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN' as i have the formula =PRODUCTION!E6&PRODUCTION!F6 and the cell shows '
How do I combine a formula and text in the same cell in excel 2010.
What I am looking for is the sum of a number of cells, followed by text.
e.g. something like =SUM(E4:E50)"/35"
If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'
How do I write this formula?
I need to combine the following formulas into one but obviously it won't work due to the limitations. Would the following be better using VBA? If yes. At the moment, they are in 3 separate columns and working but indepenantly. It basically says, if the agreement is older than todays date, write expired, if not if its for 12 months, then calculate the percantage according to number of days. and so on for 24 months and 36 months...................
View 2 Replies View RelatedIs there a way to have the answer to a formula display nothing if one of the cells have nothing. Example cell A3= A1*A2 ... it will naturally show 0.00 but even if both A1 and A2 are completely blank. Is it possible for A3 to show nothing if A1 or A2 or both contain nothing. I still want it to show 0.00 but only if that’s what is in the other cells.
View 5 Replies View RelatedI want the user to select a currency pair in cell B4, eg, EUR/USD
Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....
=MT4|BID! & cell B4 & "m"
the cell b4 is the bit i am not sure about.
I have a column say column A that holds text. If the values match say:
ABC1 or
ABC2 or
ABC3
And also match column B if it shows "Yes" then show "Correct" or "Not Correct".
I have two columns each column has a combination of the same 3 words "cash", "card", and "trade". The are formatted as "Cash & Card & Trade" or "Cash & Card" or "Card" ect. I want a formual that can combine the entries written into each columan into one column. I will put an example below.
A
B
C
D
1
Client
new
old
both (formula in this column)
2
name 1
Cash & Card & Trade
Cash & Card
Cash & Card & Trade
3
name 2
card
cash
Cash & Card
[code]....
Is there away to Combine a Text Entree in a Formula with a Formula
=IF('VEGAS - Main'!Z9=1,"MIXED DBLs","")
I am trying to do something like this:
=IF('VEGAS - Main'!Z9=1,"TEAM EVENT"+'Mixed Doubles'!P9
Result will be MIXED DBLs 1st Place
- I have excel file with data I need
- I have fixed txt(html) template that i need to integrate Excel information into
- Final result that I want to achieve is saved .txt(html) file with combination of fixed information (text) and data from excel cells.
I need to writing a VBA code for each of above (integrating text & cells, saving results as text)
In column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).
View 2 Replies View RelatedColumn A has the following values:
au:asd
au:qwe
au:zxc
etc
I want to combine the text from each cell into a text string so that each cell's text is separated by a comma, thus:
au:asd,au:qwe,au:zxc,etc
I can do this with a formula,
=a1&","&a2&","&a3
but this may exceed the 255 character limit when the text string needs to be transferred to a query.
show a formula as text in another cell
View 5 Replies View RelatedI have to following code. I have an issue now where I have to Pop a textbox from based on the answer to one of my questions.
If cboProjOffset = "Other" Then
userSalOther.Show
Problem is I actually want the text box to populate on the same line of the range answer in column m. At present it doesnt seem to be doing this. Can anyone help?
Public gintEditMode As Boolean
Public gintTotalEntries As Integer
Public gintLineNumber As Integer
Private Sub cboProjOffset_Change()
If cboProjOffset = "Other" Then
userSalOther.Show
End If
End Sub
I have a spread sheet were the area is getting very limited. I need to insert a small icon and when the mouse goes over (like it does in a form tool tip) will show the value of a cell (text value) located in another sheet in same workbook, or I was thinking inset a comment next to the icon and link the comments of the comments text to cell with the text value.
View 14 Replies View RelatedI have a spread sheet were the area is getting very limited. I need to insert a small icon and when the mouse goes over (like it does in a form tool tip) will show the value of a cell (text value) located in another sheet in same workbook, or I was thinking inset a comment next to the icon and link the comments of the comments text to cell with the text value.
I've look the properties of this to objects and can figure it out.
I am trying to write a function that will compare the text of cell "A2" to the text of cell "B2" and display cell "A2"'s text characters that didnt match from cell "B2" in column C. Can anyone help me out with writing this formula?
EX: ....
Im trying to add Cells K2,L2,M2,N2,O2
For instance Column K2 could have the number "2" in it, but in K3 it might be blank.
L2 could have the number 3 in it, but be blak in L3.
M2 could have 50 in it or could be blank.
What I want for P2 to say is 2&3, if the cells in K2 and L2 show the 2 and 3. If it has the 50 in M2 thought, I want it show it.
in the "Order for EMS" and "Order for FEDEX" there have 1 product exist, now the customer need to type the country in the "F35", and the Price of weight will look back to the sheet "EMS Weight" and "FEDEX Weight" but for example, if i type Japan in both sheet, the EMS that one is correct 1kg to Japan is $168 but in FEDEX that one is not correct 1kg to Japan is $169 but now show $342, the formula in "G36" should be correct, what's wrong of my formula????
View 3 Replies View RelatedI've got problem with combining 3 cells into one. First cell contains text, second have date yyyy-mm-dd and last one is as previous. I'm using formula .
[Code] .....
And I'm getting: TEXT_41694_41701. How can that be corrected to use formatting for date format to get TEXT_yyyy-mm-dd_yyyy-mm-dd?
I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.
It ends up being 18 variations so my IF formula just won't compute.
This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:
IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc
Any way I can get this to work for all 18 possible outcomes??
I'm looking for a way for Excel to combine formulas together into a more compact form for me.
Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.
Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?
Here is the one-cell version of a formula I work with.
[Code] .......
That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.
Attached File : Excel Formula Combination Question.xlsx‎
I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.
View 10 Replies View RelatedThe following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
I am using this formula for long now
=SUMPRODUCT(('[Diesel Statement.xlsx]Nov 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Nov 12'!$G$2:$G$251)
every month i just add a new sheet and change the month name in the formula to that eg
I changed it to
=SUMPRODUCT(('[Diesel Statement.xlsx]Dec 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Dec 12'!$G$2:$G$251)
but now it is not calculating the answer is coming as #value!.
Then I tried to add up by
=VLOOKUP($H$1,'[Diesel Statement.xlsx]Dec 12'!$C$2:$G$215,5,TRUE)
but it does not add all the entries only one entry is shown.
I want the total of the column G. looking up for values in C = H1 value
I would like to use IF function, to get the answer, if the mark is less than or equal to 20, the answer is AVERAGE, if the mark is between 21 and 25 the asnwer is GOOD, if the mark is between 26 and 30 the asnswe is VERY GOOD, if the mark is above 30 then the asnwer must be EXCELLENT.
View 4 Replies View RelatedI have a formula that is correct all the way up to adding the last *K9. here is an
example:
C9 a number 4 then add 1 to it which makes it 2 now * it by D9 which is the number 384 and the answer is 768 now * that by K9 which is 6 and you get 4608 I'm getting 2310 using this formula... =IF( C9=0, 0, C9+1*D9)*K9.
I have a excel file that tracks completion rates and has lots of formulas but there is one that I cannot figure out.
I want a formula that will display "not completed in time" (for example) if completion isn't 100% by the planned completion date. The problem I have is that I want to have a record of this and if the completion rate becomes 100% after the planned completion date it will no longer show "not completed in time". Is there a way to setup a formula that will show data in a cell if the formula has EVER met certain criteria.
I have a cell (A1) that is referenced to another cell in the workbook ('Sheet1!'B1) by an if statement. I then have another cell that I want the value to show up if there is a value in (A1), If there not a value (Shown in A1). i do not want to show the value. Howeverwith the formula I have. I can delete the (A1) reference value (which shows nothing) and replace if with either a (0 or delete the contents of the cell and it works.
A1=IF('Sheet1'!AF15="","",'Sheet1'!AF15)
B1=IF(AND(A$1>0,E108>0),(D108&E108),"")