I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.
Is this possible? I've tried to use the formula below with no luck,
=Concatenate(A1:GR1)
The "TRAINING" sheet has a formula in column "R" which checks and counts clients in the "DATA" sheet.
Inside the formula it looks for a date in Column "O" of the "DATA" sheet equal to or greater than 01/01/2014.
I would like use the date (Month) as the criteria shown in Column E of the "Training" sheet, still using Column "O" of the "DATA" sheet so that the result returns the count of only those within that month's date.
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(irow, 1).Value = Target.Value
End Sub
I am using above formula to copy. The problem being faced is that if in any sheet of the workbook is double clicked, the data is copied in sheet 2. Is it possible to amend the formula to make it only a particular sheet(query) specific instead of Workbook.
I currently have a pivot table linked into a excel query. This pivot table is refreshed everyday and looks at the following data:-
PAGE - Username
ROW - Date
DATA - Output
What I need to do on a regular basis is select the pivot table field Username and hide specific staff which are new to the table.
My question is can i create a code which will only show usernames from a given range e.g. Sheet 1 A1:A10 in this field?
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
I'm trying to use a macro to write a formula within a column of data.
Here's the
I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
E2 is 134
=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")
returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...
I am using CONCATENATE formula to make summary of my data, but i m using long CONCATENATE formula. I want to use short formula instead of CONCATENATE
formula is here :
[Code]....
How to short this formula and accuracy is first ...
Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet
PHONE
WIRE
CANDY
INDIA
When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this
PHONE WIRE CANDY INDIA
I want it to look like this, removing any blank lines within cell A1
PHONE
WIRE
CANDY
INDIA
Something wrong with the formula? how come it doesn't do both?
=SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")
I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:
=NeoTicker|q!'@ES#,last'
I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:
G10: '=NeoTicker|q!'
H10: '@ES#
I have been able to create concatenate formulas for numbes (% and $), but have been unable to create one that will display a date, I. E. for the number formula, I used, =(A8&" "&TEXT(B8,"$0")). What would be the formual so the second cell show the value as a date?
View 2 Replies View RelatedI am writing a macroI have three variables named "name", " book", and "count". In previous statements these variables have all been defined as String, String, Integer respectively. I am trying to uses all of these variables in the same formula and am encountering issues. The formula I wish to use it in is:
ActiveCell.FormulaR1C1 = "=COUNT('[" & book & "]" & name & "!'""B6:B" & count & ")"
Where if book = Book1 ; name = "Sheet1" ; and Count = 100 I would want the following result: "=COUNT('[Book1]Sheet1!B6:B100)"
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
I'm looking for a formula that utilizes an "if/then" condition. I'm attaching a sample workbook of what I'm dealing with. I need it to end up so that column "E" holds: if a equals c, then e = b, "and", d, c Does that make sense? I manually entered my desired end result into column E in the sample workbook.
View 3 Replies View RelatedI am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.
View 4 Replies View RelatedI receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)
In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.
Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.
View 13 Replies View RelatedLooking for a little help with the following formula =CONCATENATE("August 07 -",TODAY()," Evening Instructor") After entering this formula today is displayed as a serial number. I'd like it to display as current month/current day/current year (11/28/08)
View 2 Replies View RelatedIs there a way to nest IF & And statements. I'm looking to concatenate a number of cells and seperate them with a space and/or comma but only if they contain data so need something along the lines of
View 3 Replies View Relatedway of joinng the contents of cells to create a formula?
path month subdept
x:path oct [dept.xls]sheet1!a1
x:path dec [dept.xls]sheet1!a1
is there any way of joining these to create a cell reference such as x:path oct[dept.xls]sheet1!a1
i've tried the concatenate and "& &" method to no avail.
Is there a faster way to combine all the P.O# with a formula automatically, no matter how many P.O# there are? Its unpredictable on how many invoices will get pay per wire.
Log # Amount P.O. Numbers 1-a 45,678 1234 1-b 89,173 5678 1-c 66,526 9123 2 22,113 2345 3-a 66,654 6789 3-b 88,650 2468
1 201,377 1234/5678/9123 2 22,113 2345 3 155,304 6789/2468
I would like to reference information from another workbook. Instead of writing the Name of the other workbook I would like to reference through a Concatenate formula (e.g. CONCATENATE("Test"&G2&".xls")). I always get an error message. Is there any way I can reference from another workbook by using the Concatenate or any other formula?
View 5 Replies View RelatedI want to "calculate" a worksheet name by concatenating 2 cells. Then based on this concatenated word perform a " lookup". Basically I have a form for people to fill in and want to extract their answers automatically.
View 4 Replies View RelatedI have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
I am attempting to use concatenate to combine two cells of data, but to only display the information if both cells contain information.
Currently using the following simple formula:
=concatenate(a1,a2)
However, I only want it to work if both a1 and a2 cells contain data. If only a1 contains information, but a2 is blank, I want it to calculate a blank cell.
If I have the following data set in cells A1:C9:
IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE
In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):
=COUNTIF(A$2:A2,A2)=1
In cell E2, I have this formula to count unique occurrences:
=SUMPRODUCT(C2:C9*1)
In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):
=IF(ROWS(G$2:G2)
"Smith B" is text in C1.
My current formula in C3 is a simple reference to a cell in another workbook:
=+'Z:Time Records2007[Smith B.xls]Daily Time'!$C1352
I would the formula to refer to C1 to get the "Smith B" part.
This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.
To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?
=+'Z:Time Records2007["C1".xls]Daily Time'!$C1352
I have the below concatenate formula and it works for what I need..well, almost. I know I don't have the format cells option when using concatenate, but I need the answers to the formula to come out as a percentage or to two decimal places. I have searched and honestly don't understand how to do this in my formula....
View 6 Replies View RelatedI'm using excel 2003 and have a problem regarding some code.
Dit(a, b) = "=" & Hit(a, (d - 12 - e) + f) & "/" & (Pro & ".NrE.sol")
Where "Hit(a, (d - 12 - e) + f)" can be numbers with decimals.
When I run the code the result is nothing, unless the number is a number without decimals.
If I use just "Dit(a, b) = Hit(a, (d - 12 - e) + f)" it shows the right number.
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,